Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    Board Regular
    Join Date
    Apr 2007
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Amortization 360 vs 365 Days

    I have a loan for $755,812 at 6.0% for 5 years. I put this in as pmt and got a montly payment of $14,611.98. The bank has a payment of $14,644.10.

    I asked the bank why the difference and they said it was the difference between 360 days vs 365.

    How do I calculate it to get their montly payment and make an amortization schedule with interest and principal?

    Thanks

  2. #2
    New Member
    Join Date
    Sep 2009
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Your banks payment is equivalent to an AER of 6.264%. Now I can get pretty close to your banks payment with ((1+6%/360)^(365/12)-1) as monthly interest rate on the outstanding balance. This is of course a rediculous way of calculating interest. A normal APR is also rediculous but in that case it should have been ((1+6%/12)^1-1) for the monthly rate. Banks wil try to explain to you this is absolutely necessary. Except when you loan money to them. Then on your savings account you always get the qouted rate as effective annual rate.

  3. #3
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Hello John

    I can't get the bank's number exactly but this is how I would approach it:

    Sheet1

    ABCDE
    1Initial loan 755,812.00


    2Interest rate6%


    3BasisA360


    4Term5 years


    5Period12 (monthly)


    6Type0


    7




    8To convert A360 to A3656.0833%


    9




    10PMT on this basis-14,641.27


    11




    12




    13PeriodPaymentInterestCapitalOutstanding
    140


    -755,812.00
    15114,641.273,831.5510,809.72-745,002.28
    16214,641.273,776.7510,864.52-734,137.76
    17314,641.273,721.6710,919.60-723,218.16
    18414,641.273,666.3110,974.95-712,243.21
    19514,641.273,610.6811,030.59-701,212.62
    20614,641.273,554.7611,086.51-690,126.11
    21714,641.273,498.5611,142.71-678,983.39
    22814,641.273,442.0711,199.20-667,784.19
    23914,641.273,385.2911,255.97-656,528.22
    241014,641.273,328.2311,313.03-645,215.19
    251114,641.273,270.8811,370.39-633,844.80
    261214,641.273,213.2411,428.03-622,416.77
    271314,641.273,155.3111,485.96-610,930.81
    281414,641.273,097.0811,544.19-599,386.62
    291514,641.273,038.5611,602.71-587,783.91
    301614,641.272,979.7411,661.53-576,122.38
    311714,641.272,920.6211,720.65-564,401.73
    321814,641.272,861.2011,780.06-552,621.67
    331914,641.272,801.4811,839.78-540,781.89
    342014,641.272,741.4611,899.80-528,882.08
    352114,641.272,681.1411,960.13-516,921.95
    362214,641.272,620.5112,020.76-504,901.19
    372314,641.272,559.5712,081.70-492,819.49
    382414,641.272,498.3212,142.95-480,676.54
    392514,641.272,436.7612,204.51-468,472.04
    402614,641.272,374.8912,266.38-456,205.66
    412714,641.272,312.7112,328.56-443,877.10
    422814,641.272,250.2112,391.06-431,486.05
    432914,641.272,187.3912,453.87-419,032.17
    443014,641.272,124.2612,517.01-406,515.16
    453114,641.272,060.8112,580.46-393,934.70
    463214,641.271,997.0312,644.24-381,290.46
    473314,641.271,932.9312,708.34-368,582.13
    483414,641.271,868.5112,772.76-355,809.37
    493514,641.271,803.7612,837.51-342,971.85
    503614,641.271,738.6812,902.59-330,069.26
    513714,641.271,673.2712,968.00-317,101.26
    523814,641.271,607.5313,033.74-304,067.52
    533914,641.271,541.4513,099.81-290,967.71
    544014,641.271,475.0413,166.22-277,801.48
    554114,641.271,408.3013,232.97-264,568.51
    564214,641.271,341.2213,300.05-251,268.46
    574314,641.271,273.7913,367.48-237,900.98
    584414,641.271,206.0313,435.24-224,465.74
    594514,641.271,137.9213,503.35-210,962.39
    604614,641.271,069.4613,571.81-197,390.58
    614714,641.271,000.6613,640.61-183,749.98
    624814,641.27931.5113,709.76-170,040.22
    634914,641.27862.0113,779.26-156,260.96
    645014,641.27792.1613,849.11-142,411.85
    655114,641.27721.9513,919.32-128,492.53
    665214,641.27651.3913,989.88-114,502.64
    675314,641.27580.4614,060.80-100,441.84
    685414,641.27509.1814,132.08-86,309.76
    695514,641.27437.5414,203.73-72,106.03
    705614,641.27365.5414,275.73-57,830.30
    715714,641.27293.1714,348.10-43,482.20
    725814,641.27220.4314,420.84-29,061.36
    735914,641.27147.3214,493.94-14,567.42
    746014,641.2773.8514,567.420.00
    Excel 2003

    Worksheet Formulas
    CellFormula
    B8=B2*365/360
    B10=PMT(B8/12,B4*B5,B1,0,B6)
    E14=-B1
    B15=-$B$10
    C15=E14*$B$8/12*-1
    D15=B15-C15
    E15=E14+D15
    Richard Schollar

    Using xl2013

  4. #4
    New Member
    Join Date
    Mar 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Can you help me with the formulas on a actual/365 (fixed) loan amortization? Thanks. $20,000,000 loan; 3.25% interest rate; 25 year amortization; monthly payments

  5. #5
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Here you go:

    Excel 2010
    AB
    1Loan$20,000,000
    2Rate3.25%
    3Term25
    4
    5PMT-$97,463

    Sheet1



    Worksheet Formulas
    CellFormula
    B5=PMT(B2/12,B3*12,B1)

    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  6. #6
    New Member
    Join Date
    Mar 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Amortization 360 vs 365 Days

    Thanks. What is the difference between that formula and one used 30/360? I guess I am struggling with the correct formula to use for the various amortization schedules. I appreciate your help.

  7. #7
    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 they are doing an actual day (365 days per year) calculation then it is very possible that they are assuming actual days for each month rather than a month being 1/12 of a year. In that case the interest would be daily and the days between each payment would vary depending upon the number of days in the month. This is a common practice for consumer loans as opposed to mortgages. If that is the case you have to amortize the loan and iterate on the payment rather than solving it in closed form.

  8. #8
    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

    Can someone help me figure out a mortgage payment formula in excel using the PMT function? I have a $11,000,000 loan, interest rate is 3.99%, amortization is 25 years. The bank is telling me the monthly payment is $58,360.23, but I cannot get the same result using the pmt calculation. Any help is appreciated...

  9. #9
    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

    I get $58,001.33 both with PMT function and with my HP 12C financial calculator.
    Using Excel 2010

  10. #10
    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 BadgerRoad View Post
    I get $58,001.33 both with PMT function and with my HP 12C financial calculator.
    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.

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
  •