Remaining Balance Formula for Actual/360 and Actual/Actual Accrual Methods

Question

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!

Chris Degnen · Answer

The following formula will give the balance b in month n

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

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

Remaining Balance Formula for Actual/360 and Actual/Actual Accrual Methods

One Answer

Add your own answers!

Ask a Question