HELOC Calculator

[Flouf]

[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]

 

[StephenCrump]

Welcome to the Forum!

Can you be a little more specific about what you're trying to do, and the bit you're having trouble with?
It would help if you could post a screenshot. See Part B here for a couple of ways you can do this: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Flouf]

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.

[TABLE="width: 343"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Current Balance[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]Interest Rate (APR)[/TD]
[TD="align: right"]7.50%[/TD]
[/TR]
[TR]
[TD]Rate change (per year)[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Payoff goal (in months)[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Net Monthly Income (Pmt)[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Monthly Expenses (Increase Principal)[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Current Payment[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]New Payment[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

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.

Excel 2010
	A	B	C	D	E	F	G
1	Amount	1,000,000
2	i	7.50%
3	Increment	5.00%
4	Fee	200
5	Expenses	1,000
6	Repayment	46,596.47
7
8	time	Start Bal	Expenses	Fee	Interest	Repayment	End Bal
9	1	1,000,000.00	1,000.00		6,256.25	-46,596.47	960,659.78
10	2	960,659.78	1,000.00		6,010.37	-46,596.47	921,073.69
11	3	921,073.69	1,000.00		5,762.96	-46,596.47	881,240.18
12	4	881,240.18	1,000.00		5,514.00	-46,596.47	841,157.72
13	5	841,157.72	1,000.00		5,263.49	-46,596.47	800,824.74
14	6	800,824.74	1,000.00		5,011.40	-46,596.47	760,239.67
15	7	760,239.67	1,000.00		4,757.75	-46,596.47	719,400.95
16	8	719,400.95	1,000.00		4,502.51	-46,596.47	678,306.99
17	9	678,306.99	1,000.00		4,245.67	-46,596.47	636,956.19
18	10	636,956.19	1,000.00		3,987.23	-46,596.47	595,346.95
19	11	595,346.95	1,000.00		3,727.17	-46,596.47	553,477.65
20	12	553,477.65	1,000.00	200.00	3,466.74	-46,596.47	511,547.92
21	13	511,547.92	1,000.00		5,339.04	-46,596.47	471,290.50
22	14	471,290.50	1,000.00		4,919.69	-46,596.47	430,613.72
23	15	430,613.72	1,000.00		4,495.98	-46,596.47	389,513.23
24	16	389,513.23	1,000.00		4,067.85	-46,596.47	347,984.61
25	17	347,984.61	1,000.00		3,635.26	-46,596.47	306,023.40
26	18	306,023.40	1,000.00		3,198.16	-46,596.47	263,625.09
27	19	263,625.09	1,000.00		2,756.51	-46,596.47	220,785.14
28	20	220,785.14	1,000.00		2,310.26	-46,596.47	177,498.93
29	21	177,498.93	1,000.00		1,859.36	-46,596.47	133,761.83
30	22	133,761.83	1,000.00		1,403.77	-46,596.47	89,569.13
31	23	89,569.13	1,000.00		943.43	-46,596.47	44,916.09
32	24	44,916.09	1,000.00	200.00	480.38	-46,596.47	0.00
Sheet1