Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Mortgage Comparison

  1. #1
    New Member
    Join Date
    Jan 2006
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,351
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    1,568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by joeu2004; Aug 13th, 2017 at 05:18 PM. Reason: Note #4

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    1,568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mortgage Comparison

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com