Thanks:  0
Likes:  0

1. ## Mortgage Comparison

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.

#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">
BC
6Option One
7Loan Amount (inc any fees added to loan)
8Other Fees (not added to loan)
9Term of Mortgage (in years)
10Initial Interest Rate
11Term of Initial Interest Rate (in months)
12Monthly Mortgage Payment#NUM!
13Amortised Balance at end of Initial Interest Rate Period
14
15Option Two
16Loan Amount (inc any fees added to loan)
17Other Fees (not added to loan)
18Term of Mortgage (in years)
19Initial Interest Rate
20Term of Initial Interest Rate (in months)
21Monthly Mortgage Payment#NUM!
22Amortised Balance at end of Initial Interest Rate Period
23
24
25Option One Total Cost Over Intial Rate Period#NUM!
26Option Two Total Cost Over Initial Rate period#NUM!
27Difference#NUM!

#DAE7F5 ;color: #161120">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

2. ## Re: Mortgage Comparison

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.

3. ## Re: Mortgage Comparison

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.

4. ## Re: Mortgage Comparison

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

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

Formulas
Cell Formula
D9 =C9*12
D10 =C10/12
C12 =IFERROR(PMT(D10,D9,-C7),0)
C13 =FV(D10,C11,C12,-C7)
C14 =12*D14
D14 =IFERROR(RATE(D9,C12,-C7+C8),0)
D18 =D9
D19 =C19/12
C21 =IFERROR(PMT(D19,D18,-C16),0)
C22 =FV(D19,C20,C21,-C16)
C23 =12*D23
D23 =IFERROR(RATE(D18,C21,-C16+C17),0)
C25 =C12*C11-(C7-C13)+C8
D25 =C25-C8
C26 =C21*C20-(C16-C22)+C17
D26 =C26-C17
C27 =C25-C26
D27 =D25-D26
C29 =C12*D9-C7+C8
D29 =C29-C8
C30 =C21*D18-C16+C17
D30 =C30-C17
C31 =C29-C30
D31 =D29-D30

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.

5. ## Re: Mortgage Comparison

Typo, too late to edit....
Originally Posted by joeu2004
Cell Formula
D18 =D9
The formula in D18 should be =C18*12.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•