HELOC Calculator

Flouf

New Member
Joined
Apr 22, 2017
Messages
2
[FONT=&quot]Hi All,

I'm attempting to recreate this Line of Credit calculator in Excel.
Source: Home Equity Line of Credit (HELOC) Calculator | How To Pay Off Your Mortgage Early With A Home Equity Line of Credit (HELOC)[/FONT]
[FONT=&quot]
I know how to do a basic pmt formula, but am having trouble figuring out how to factor in the additional fields:[/FONT]

  • Rate Change (per year)
  • Monthly Expenses (set increase of principal per month)
  • Annual Fee
[FONT=&quot]
I think I may be able to do this if I build an amortization type table, but I don't want to have to do that. I want to be able to change the numbers easily on the fly.[/FONT]
[FONT=&quot]Part of the issue is I don't know the actual formula if I were to do this on paper.[/FONT]
[FONT=&quot]Thank you for helping,

-Flouf[/FONT]
 

Flouf

New Member
Joined
Apr 22, 2017
Messages
2
Thanks for the welcome.

I am trying to create an offline version of the calculator I linked above. I need to be able to do all the same calculations in Excel. I don't need the graphs or any of that, just the calculations. The trouble I am having is that I don't know how to do it on paper, so I'm having trouble doing it in Excel. Below is what I have so far, and it isn't much. I don't have formulas entered for anything yet.

I know how to calculate a basic PMT formula based on the current principal, APR and term, but I am confused about how to enter in the additional factors:
  • Rate Change (per year)
  • Monthly Expenses (set increase of principal per month)
  • Annual Fee

At first I thought I could treat it the same was as if you were making additional monthly payments, but using a negative. I don't think that will work. Ideally I don't want to do an amortization table, but I honestly have no clue what to do.

Current Balance2000
Interest Rate (APR)7.50%
Rate change (per year)
Payoff goal (in months)24
Net Monthly Income (Pmt)
Monthly Expenses (Increase Principal)
Current Payment$0.00
New Payment


<colgroup><col><col></colgroup><tbody>
</tbody>
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,563
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Amount</td><td style="text-align: right;;">1,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">i</td><td style="text-align: right;;">7.50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Increment</td><td style="text-align: right;;">5.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Fee</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Expenses</td><td style="text-align: right;;">1,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Repayment</td><td style="text-align: right;;">46,596.47</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;;">time</td><td style="font-weight: bold;text-align: right;;">Start Bal</td><td style="font-weight: bold;text-align: right;;">Expenses</td><td style="font-weight: bold;text-align: right;;">Fee</td><td style="font-weight: bold;text-align: right;;">Interest</td><td style="font-weight: bold;text-align: right;;">Repayment</td><td style="font-weight: bold;text-align: right;;">End Bal</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">1</td><td style="text-align: right;;">1,000,000.00</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">6,256.25</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">960,659.78</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">2</td><td style="text-align: right;;">960,659.78</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">6,010.37</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">921,073.69</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">3</td><td style="text-align: right;;">921,073.69</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">5,762.96</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">881,240.18</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">4</td><td style="text-align: right;;">881,240.18</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">5,514.00</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">841,157.72</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">5</td><td style="text-align: right;;">841,157.72</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">5,263.49</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">800,824.74</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">6</td><td style="text-align: right;;">800,824.74</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">5,011.40</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">760,239.67</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">7</td><td style="text-align: right;;">760,239.67</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4,757.75</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">719,400.95</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">8</td><td style="text-align: right;;">719,400.95</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4,502.51</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">678,306.99</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">9</td><td style="text-align: right;;">678,306.99</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4,245.67</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">636,956.19</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">10</td><td style="text-align: right;;">636,956.19</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">3,987.23</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">595,346.95</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">11</td><td style="text-align: right;;">595,346.95</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">3,727.17</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">553,477.65</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">12</td><td style="text-align: right;;">553,477.65</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">200.00</td><td style="text-align: right;;">3,466.74</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">511,547.92</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">13</td><td style="text-align: right;;">511,547.92</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">5,339.04</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">471,290.50</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">14</td><td style="text-align: right;;">471,290.50</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4,919.69</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">430,613.72</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">15</td><td style="text-align: right;;">430,613.72</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4,495.98</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">389,513.23</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">16</td><td style="text-align: right;;">389,513.23</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4,067.85</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">347,984.61</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">17</td><td style="text-align: right;;">347,984.61</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">3,635.26</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">306,023.40</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">18</td><td style="text-align: right;;">306,023.40</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">3,198.16</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">263,625.09</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">19</td><td style="text-align: right;;">263,625.09</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">2,756.51</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">220,785.14</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">20</td><td style="text-align: right;;">220,785.14</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">2,310.26</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">177,498.93</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">21</td><td style="text-align: right;;">177,498.93</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1,859.36</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">133,761.83</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">22</td><td style="text-align: right;;">133,761.83</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1,403.77</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">89,569.13</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">23</td><td style="text-align: right;;">89,569.13</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">943.43</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">44,916.09</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">24</td><td style="text-align: right;;">44,916.09</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">200.00</td><td style="text-align: right;;">480.38</td><td style="text-align: right;;">-46,596.47</td><td style="text-align: right;;">0.00</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Forum statistics

Threads
1,085,317
Messages
5,382,931
Members
401,810
Latest member
ibusyed

Some videos you may like

This Week's Hot Topics

Top