Heloc payment calculator principal and interest excel

Here's a sample amortisation schedule.

The interest increment is just an allowance for the interest rate to vary into the future. In this example, it is assumed that the 7.5% p.a. rate will increase by 5%, i.e. to 12.5% p.a. in year 2. (And presumably so on into the future, but I haven't checked terms longer than two years).

The calculator assumes:
- monthly repayments in arrears
- monthly expenses drawn in advance
- fees payable annually in arrears (but to replicate the calculator I need to assume the first year's fee is payable at the start of month 12, i.e. after 11 months, and the second fee is payable after 23 months, which is a little odd.

Hence the interest calculation in E9 is: =(i+Inc*INT((A9-1)/12))/12*(B9+C9+D9)

Hopefully you can replicate the rest of the the amortisation schedule?

We can use GoalSeek on the Repayment to produce a nil end balance. The value is shown here only to 2 decimal places, but the calculations use the exact value.

Alternatively, you can derive the results algebraically. At t=12 months, the value of future repayments of 1 per month will be PV((i+Inc)/12,12,1), which has a value at t=0 of PV((i+Inc)/12,12,1)/(1+i/12)^12

So, ignoring expenses and fees, the monthly repayment required will be:

=-Amount/(PV((i+Inc)/12,12,1)/(1+i/12)^12+PV(i/12,12,1))

=45,572.18

Similarly, the value at t=0 of expenses =-(PV((i+Inc)/12,12,Expenses,,1)/(1+i/12)^12+PV(i/12,12,Expenses,,1))
And fees =Fee/(1+i/12)^11*(1+1/(1+i/12)/(1+(i+Inc)/12)^11)

Putting it all together, the level repayment required is:

=-(Amount-(PV((i+Inc)/12,12,Expenses,,1)/(1+i/12)^12+PV(i/12,12,Expenses,,1))+Fee/(1+i/12)^11*(1+1/(1+i/12)/(1+(i+Inc)/12)^11))/(PV((i+Inc)/12,12,1)/(1+i/12)^12+PV(i/12,12,1))

= 46,596.47 = Amortisation schedule = on-line calculator.

The algebra will get more complicated for longer loan terms if you allow the interest rate to vary.