Mortgage Comparison

[Jackewing98]

Hello

I have a mortgage comparison spreadsheet which, once the information has been input, compares the total amount payable over the initial fixed rate e.g. two years.

Book1
	B	C
6	Option One
7	Loan Amount (inc any fees added to loan)
8	Other Fees (not added to loan)
9	Term of Mortgage (in years)
10	Initial Interest Rate
11	Term of Initial Interest Rate (in months)
12	Monthly Mortgage Payment	#NUM!
13	Amortised Balance at end of Initial Interest Rate Period
14
15	Option Two
16	Loan Amount (inc any fees added to loan)
17	Other Fees (not added to loan)
18	Term of Mortgage (in years)
19	Initial Interest Rate
20	Term of Initial Interest Rate (in months)
21	Monthly Mortgage Payment	#NUM!
22	Amortised Balance at end of Initial Interest Rate Period
23
24
25	Option One Total Cost Over Intial Rate Period	#NUM!
26	Option Two Total Cost Over Initial Rate period	#NUM!
27	Difference	#NUM!
Repayment

However, in order to calculate what the amortised balance is after the initial period you have to do a separate calculation on a separate worksheet using Excel's loan amortisation template. What I would like to be able to do is get the above to calculate the correct amortised balance without using a separate worksheet to make it simpler. However, as the initial preference rate will be variable it needs to be able to calculate the correct number of months based on what has been input. Does anyone know if there is a way to do this?

Thanks

 

[merlin777]

sometimes doing it separately is the simpler way - and easier to maintain if it's not working properly or needs changing. I often do it on the same sheet then hide those columns.

 

[shg]

You could modify the Excel template to give the desired results using your inputs -- or make your own.

Once you do that, you could add another sheet with a data table that would allow you to compare as many loans as you like using that same page for calculation.

I'm happy to help with the second part, not the first.

 

[joeu2004]

The table entries can be calculated directly, as demonstrated below. See the notes following the tables.

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH][/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]OPTION ONE
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]Loan amt
[/TD]
[TD="align: right"]100,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]Loan fees
[/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]Term (yrs)
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]180
[/TD]
[TD]mos
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]Init int rate (annl)
[/TD]
[TD="align: right"]4.00%
[/TD]
[TD="align: right"]0.3333%
[/TD]
[TD]per mo
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]Init term (mos)
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]Pmt (per mo)
[/TD]
[TD="align: right"]739.69
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD]Init term end bal
[/TD]
[TD="align: right"]95,033.35
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD]APR
[/TD]
[TD="align: right"]4.15%
[/TD]
[TD="align: right"]0.3457%
[/TD]
[TD]per mo
[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: center"]OPTION TWO
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD]Loan amt
[/TD]
[TD="align: right"]100,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD]Loan fees
[/TD]
[TD="align: right"]2,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD]Term (yrs)
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]180
[/TD]
[TD]mos
[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD]Init int rate (annl)
[/TD]
[TD="align: right"]3.50%
[/TD]
[TD="align: right"]0.2917%
[/TD]
[TD]per mo
[/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD]Init term (mos)
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21
[/TD]
[TD]Pmt (per mo)
[/TD]
[TD="align: right"]714.88
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD]Init term end bal
[/TD]
[TD="align: right"]94,839.14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]23
[/TD]
[TD]APR
[/TD]
[TD="align: right"]3.80%
[/TD]
[TD="align: right"]0.3163%
[/TD]
[TD]per mo
[/TD]
[/TR]
[TR]
[TD="align: center"]24
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]25
[/TD]
[TD]Init term cost, opt 1
[/TD]
[TD="align: right"]4,909.60
[/TD]
[TD="align: right"]3,909.60
[/TD]
[TD]int
[/TD]
[/TR]
[TR]
[TD="align: center"]26
[/TD]
[TD]Init term cost, opt 2
[/TD]
[TD="align: right"]5,417.73
[/TD]
[TD="align: right"]3,417.73
[/TD]
[TD]int
[/TD]
[/TR]
[TR]
[TD="align: center"]27
[/TD]
[TD]Diff, opt 1
[/TD]
[TD="align: right"]-508.13
[/TD]
[TD="align: right"]491.87
[/TD]
[TD]int
[/TD]
[/TR]
[TR]
[TD="align: center"]28
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]29
[/TD]
[TD]Total cost, opt 1
[/TD]
[TD="align: right"]34,143.83
[/TD]
[TD="align: right"]33,143.83
[/TD]
[TD]int
[/TD]
[/TR]
[TR]
[TD="align: center"]30
[/TD]
[TD]Total cost, opt 2
[/TD]
[TD="align: right"]30,678.86
[/TD]
[TD="align: right"]28,678.86
[/TD]
[TD]int
[/TD]
[/TR]
[TR]
[TD="align: center"]31
[/TD]
[TD]Diff, opt 1
[/TD]
[TD="align: right"]3,464.97
[/TD]
[TD="align: right"]4,464.97
[/TD]
[TD]int
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]D9
[/TH]
[TD="align: left"]=C9*12
[/TD]
[/TR]
[TR]
[TH="width: 10"]D10
[/TH]
[TD="align: left"]=C10/12
[/TD]
[/TR]
[TR]
[TH="width: 10"]C12
[/TH]
[TD="align: left"]=IFERROR(PMT(D10,D9,-C7),0)
[/TD]
[/TR]
[TR]
[TH="width: 10"]C13
[/TH]
[TD="align: left"]=FV(D10,C11,C12,-C7)
[/TD]
[/TR]
[TR]
[TH="width: 10"]C14
[/TH]
[TD="align: left"]=12*D14
[/TD]
[/TR]
[TR]
[TH="width: 10"]D14
[/TH]
[TD="align: left"]=IFERROR(RATE(D9,C12,-C7+C8),0)
[/TD]
[/TR]
[TR]
[TH="width: 10"]D18
[/TH]
[TD="align: left"]=D9
[/TD]
[/TR]
[TR]
[TH="width: 10"]D19
[/TH]
[TD="align: left"]=C19/12
[/TD]
[/TR]
[TR]
[TH="width: 10"]C21
[/TH]
[TD="align: left"]=IFERROR(PMT(D19,D18,-C16),0)
[/TD]
[/TR]
[TR]
[TH="width: 10"]C22
[/TH]
[TD="align: left"]=FV(D19,C20,C21,-C16)
[/TD]
[/TR]
[TR]
[TH="width: 10"]C23
[/TH]
[TD="align: left"]=12*D23
[/TD]
[/TR]
[TR]
[TH="width: 10"]D23
[/TH]
[TD="align: left"]=IFERROR(RATE(D18,C21,-C16+C17),0)
[/TD]
[/TR]
[TR]
[TH="width: 10"]C25
[/TH]
[TD="align: left"]=C12*C11-(C7-C13)+C8
[/TD]
[/TR]
[TR]
[TH="width: 10"]D25
[/TH]
[TD="align: left"]=C25-C8
[/TD]
[/TR]
[TR]
[TH="width: 10"]C26
[/TH]
[TD="align: left"]=C21*C20-(C16-C22)+C17
[/TD]
[/TR]
[TR]
[TH="width: 10"]D26
[/TH]
[TD="align: left"]=C26-C17
[/TD]
[/TR]
[TR]
[TH="width: 10"]C27
[/TH]
[TD="align: left"]=C25-C26
[/TD]
[/TR]
[TR]
[TH="width: 10"]D27
[/TH]
[TD="align: left"]=D25-D26
[/TD]
[/TR]
[TR]
[TH="width: 10"]C29
[/TH]
[TD="align: left"]=C12*D9-C7+C8
[/TD]
[/TR]
[TR]
[TH="width: 10"]D29
[/TH]
[TD="align: left"]=C29-C8
[/TD]
[/TR]
[TR]
[TH="width: 10"]C30
[/TH]
[TD="align: left"]=C21*D18-C16+C17
[/TD]
[/TR]
[TR]
[TH="width: 10"]D30
[/TH]
[TD="align: left"]=C30-C17
[/TD]
[/TR]
[TR]
[TH="width: 10"]C31
[/TH]
[TD="align: left"]=C29-C30
[/TD]
[/TR]
[TR]
[TH="width: 10"]D31
[/TH]
[TD="align: left"]=D29-D30
[/TD]
[/TR]
</tbody>[/TABLE]

Notes
------
1. Monthly mortgage interest rates in D10 and D19 are based on US rules. Many other countries follow the same rule, notably AU and NZ. But some countries specify a compounded annual rate, notably many EU countries. In those cases, the monthly interest rate in D10 would be (1+C10)^(1/12)-1. And Canada specifies an annual rate that is compounded monthly semi-annually(!). In that case, the monthly interest rate in D10 would be (1+C10/2)^(1/6)-1.

Likewise, the APR in C14 is 12*D14, (1+D14)^12-1, or ((1+D14)^6-1)*2 respectively. Similarly for C23. I added the APR because that is how many people compare loans.


2. PMT and APR are based on the full loan term, not the initial interest rate term. For subsequent interest rate terms, we would use the beginning outstanding balance and the length of the remaining full loan term.


3. For the initial term costs, column C includes the up-front loan fees (B8 and B17). Column D is the interest only.

Similarly for the total costs. I added the total costs because it is dubious to compare only the initial term costs, as demonstrated by the example. Although option 1 is less costly for the initial term, it is more costly for the full loan term, if we assume the initial interest rate for the full loan term.


4. In reality, we would round PMT at least to 2 decimal places, since it is real currency. However, the rounding procedure varies according to the lender. Moreover, rounding PMT results in an irregular final payment, which complicates the calculation of total costs.

 

[joeu2004]

Typo, too late to edit....

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]D18
[/TH]
[TD="align: left"]=D9
[/TD]
[/TR]
</tbody>[/TABLE]

The formula in D18 should be =C18*12.