Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Amortization 360 vs 365 Days
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,429
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Quote Originally Posted by japes View Post
    Thanks. I get this as well, but the bank says the payment is $58,360.23. I was hoping someone can show me how they are arriving at that number.
    Ask your teacher!

    If you are taking out a loan of $11M, you should have enough clout to ask the bank to detail the calculations. Alternatively, you should have an attorney or CPA on retainer who can explain the calculation or encourage the bank to.

    It might help to know what country the loan is in. For example, Canada, the EU and the USA each has a very different way of specifying interest rates. (But I've tried them all, though, to no avail.)

    I suspect there is some misunderstanding. For example, perhaps the actual interest rate is about 4.049025%, and 3.99% is some sort of theoretical rate. Or $58,360.23 is composed of $58,001.33 to pay off the loan plus some other monthly fees (e.g. PMI).

    But I will continue to see if there is a "simpler" explanation.

    PS.... In the future, it is better to start a new discussion instead of piggybacking an old one, especially when you have a very different question.
    Last edited by joeu2004; Jun 19th, 2014 at 10:59 PM.

  2. #12
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,429
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    PS (too late to edit)....
    Quote Originally Posted by joeu2004 View Post
    It might help to know what country the loan is in. For example, Canada, the EU and the USA each has a very different way of specifying interest rates. (But I've tried them all, though, to no avail.)
    It might also help to know the date when you receive the loan funds, and the date of the first payment.

    Usually, the first payment is one month after receiving the loan funds. But an "odd" period might explain the disparity between the stated interest rate and the fixed monthly payments.

    Also, is this a fixed-rate or variable-rate loan?

    In theory, that should not affect the calculation of the payment during initial term. But it might the source of some misunderstandings.

  3. #13
    New Member
    Join Date
    Jun 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Quote Originally Posted by joeu2004 View Post
    PS (too late to edit)....


    It might also help to know the date when you receive the loan funds, and the date of the first payment.

    Usually, the first payment is one month after receiving the loan funds. But an "odd" period might explain the disparity between the stated interest rate and the fixed monthly payments.

    Also, is this a fixed-rate or variable-rate loan?

    In theory, that should not affect the calculation of the payment during initial term. But it might the source of some misunderstandings.
    This is in the US. Fixed rate loan and the first payment is one month after receiving the funds. Actual / 360, interest paid for the actual number of days in each month.

  4. #14
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Quote Originally Posted by japes View Post
    The bank says the payment is $58,360.23. I was hoping someone can show me how they are arriving at that number.
    I've had experience replicating these calculations across several financial institutions. Typically, the larger the institution, the more sophisticated the calculation algorithm. It's always possible to replicate calculations to the cent, but only when you know exactly how the calculation works.

    On the face of it, the quoted repayment is too high, i.e. it's too big to be explained by slight differences in calculation methodology. As others have pointed out, perhaps the interest rate you're quoting isn't the "real" interest rate.

    There are other possibilities, e.g. there may be an application fee capitalised into the loan amount, or monthly administration fees included in the repayments.

    In summary, there are too many unknowns. If you clarify with your loan provider exactly how the calculation has been made, it will be easier to verify accurately.

  5. #15
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,429
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Quote Originally Posted by japes View Post
    This is in the US. Fixed rate loan and the first payment is one month after receiving the funds. Actual / 360, interest paid for the actual number of days in each month.
    I can confirm that those facts are probably correct.

    Refer to "japes amort sched.xls", downloadable from https://app.box.com/s/tr1i738yxcb5d0sf5ssh. Ignore any preview errors and just download.

    When I set up an amortization schedule (below) with the monthly payment of 58,360.23 and monthly interest based on actual/360, Solver determines that the actual annual interest rate is between 3.99045988210% and 3.99045988522%.

    Note that it rounds to 3.99%. Such differences between documented and actual interest rates are permitted by US "Truth in Lending" regulations (Reg Z).

    Also note that we cannot calculate the actual interest rate using Excel RATE because the function effectively calculates a monthly rate based on 30/360.

    And we cannot calculate the actual payment using Excel PMT, in part because we did not know the exact interest rate used by the bank, and in part because the function calculates payment to the full machine precision, not the real-world rounding to 2 decimal places.

    The following demonstrates how to set up the amortization schedule.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Loan
    11,000,000.00
    Pmt#
    Pmt Int Bal

    2
    Term 300 months
    6/1/2014

    11,000,000.00

    3
    Annl_rate 3.99045988365865%
    1 7/1/2014 58,360.23 36,579.22 10,978,218.99
    4
    Actl pmt 58,360.23
    2 8/1/2014 58,360.23 37,723.68 10,957,582.43
    5
    Min rate 3.99045988210%
    3 9/1/2014 58,360.23 37,652.77 10,936,874.97
    6
    Max rate 3.99045988522%
    4 10/1/2014 58,360.23 36,369.30 10,914,884.04










    300



    298 4/1/2039 58,360.23 597.55 116,134.42
    301



    299 5/1/2039 58,360.23 386.19 58,160.38
    302



    300 6/1/2039 58,360.23 199.85 0.00
    303







    2.59E-07 Actl_bal

    Formulas:

    E3: =EDATE($E$2,D3)
    F3: =$B$4
    G3: =H2*(E3-E2)*$B$3/360
    H3: =H2+G3-F3

    Copy E3:H3 down through row 302.

    -----

    Note that for comparison purposes, the Reg Z annual interest rate (based on 30/360) is 4.04902518141567% = 12*RATE(300,58360.23,-11000000).

    The difference between that and the bank's actual annual interest rate (based on actual/360) is about 0.059%, which is within the 0.125% difference permitted by Reg Z.
    Last edited by joeu2004; Jun 20th, 2014 at 02:03 AM. Reason: cosmetic

  6. #16
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,429
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    PS....
    Quote Originally Posted by joeu2004 View Post
    When I set up an amortization schedule (below) with the monthly payment of 58,360.23 and monthly interest based on actual/360, Solver determines that the actual annual interest rate is between 3.99045988210% and 3.99045988522%.
    If this is a real-world example, it is more likely the annual interest rate is 3.990459% or 3.990460%. That makes the last payment slightly less or more, which is not unusual.

    Quote Originally Posted by joeu2004 View Post
    Note that it rounds to 3.99%. Such differences between documented and actual interest rates are permitted by US "Truth in Lending" regulations (Reg Z).
    [....]
    Note that for comparison purposes, the Reg Z annual interest rate (based on 30/360) is 4.04902518141567% = 12*RATE(300,58360.23,-11000000).

    The difference between that and the bank's actual annual interest rate (based on actual/360) is about 0.059%, which is within the 0.125% difference permitted by Reg Z.
    Then again, I'm probably taking this just a little too seriously.

  7. #17
    Board Regular
    Join Date
    Dec 2005
    Location
    Wisconsin
    Posts
    211
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    You may need to ask them what interest assumptions they are using. They may be using something like a 360/365 assumption which you can't do with the PMT function.
    Using Excel 2010

  8. #18
    Board Regular
    Join Date
    Dec 2005
    Location
    Wisconsin
    Posts
    211
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    If this is actual/360 (I didn't see your post prior to my previous post) then it looks correct. I wrote an amortization schedule to do actual/360 and got a payment of 58,357.39. That payment will change depending upon the loan date.
    Using Excel 2010

  9. #19
    New Member
    Join Date
    Apr 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    If you round cell B8 to 4 places, you come up with the bank's amount: =round(B2*365/360,4)

Some videos you may like

User Tag List

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
  •