1

Is there a concise formula for calculating the remaining balance of a loan with actual/360 and actual/actual accruals?

I know for 30/360 amortizations, the remaining balance is just the FV of the principle less the FV of the loan payment annuity.

// Remaining balance for a loan amount of "princ" // and a monthly payment of "coupon" using a 30/360 accrual

Remaining Balance = princ * (1+r)^n - coupon * [((1+r)^n - 1) / r] I know with actuals, we can't easily use the annuity formula, since we don't have level payments.

I was hoping there was a formula that can take into account these "errors", where the annuity assumes 30 day, missing the day in 31, compound the error at the same rate, allowing us to still calculate the accurate remaining balance.

Or do we have to build a full amortization schedule to compute?

For a refresher on accrual methods, here is a terrific blog post explaining the differences

Thank you!

3
  • You haven't posted the blog link. Jul 27 at 13:18
  • If the interest payment is (outstanding balance)*(days e.g. 31)*(annual rate)/360, how is the payment to principal calculated? Or are the monthly payments all the same, with the portion of payment to principal varying in complement to the interest payment? Jul 27 at 13:20
  • @ChrisDegnen I added blog link. You got it - the monthly stays the same, but the interest amount varies month to month (rate / 28, rate / 30, rate / 31) and therefore the principle amount changes too. All accrual methods feature a fixed monthly payment and all must amortize to zero by loan end. But they have different principle amortization curves to get there.
    – ZAR
    Jul 27 at 20:48
1

The following formula will give the balance b in month n

eqn

where

s is the loan principal
d is the periodic payment
x[k] is the periodic rate in month k

For example. First calculating some values for a 10% 30/360 case, where

r is the 30/360 periodic rate
m is the number of periods

s = 1000
r = 0.1*30/360
m = 12

d = r s/(1 - (1 + r)^-m) = 87.9159

The balance in month n is given by

b = (d + (1 + r)^n (r s - d))/r

or the first formula can be used, with

x[1] = r
x[2] = r
...
x[12] = r

In both cases with n = 12 the balance b is zero.

Now using varying rates according to actual/360:

x[1] = 0.1*31/360
x[2] = 0.1*28/360
...
x[12] = 0.1*31/360

With n = 12 the balance b is no longer zero, as expected.

b = 0.568906

Setting b equal to zero and solving numerically for d yields

d = 87.9611

So for example, the actual/360 balance in month 7 can be found

n = 7
b = 428.809

Checking with an Excel amortization

enter image description here

There is an example of using a product function in Excel here.

4
  • Thank you @ChrisDegnan for the answer. I was hoping for a formula that didn't involve a loop, since it doesn't scale well - but yours is the correct answer by loop. Unfortunately for my effort, I think a loop may be the only way.
    – ZAR
    Jul 28 at 13:42
  • Do you mean a loop to solve for the payment amount d? That reminds me to add an Excel solver example. Perhaps you mean a loop for the products and summation. If you put it together in Excel I hope you will add a screenshot to your post. Jul 28 at 14:05
  • Just the opposite - I know how to solve via loop, I'm looking for a non-loop. Something akin to the FV of the series of payments + error between 30 and ACT. The excel version is a loop - even without solver, it is still iteratively going through each period.
    – ZAR
    Jul 29 at 15:06
  • The FV summation with a constant interest term and payment amount can be expressed in a closed form by induction, as shown here. When the interest term or payment amount varies the closed form cannot be obtained in this way. Jul 29 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.