+ Reply to Thread
Results 1 to 10 of 10

PMT Formula Dysfunction

  1. #1
    Registered User
    Join Date
    01-31-2018
    Location
    Alabama, USA
    MS-Off Ver
    MS 2016
    Posts
    3

    PMT Formula Dysfunction

    I am having trouble using the PMT function. When I calculate my formula out to the end of the loan period (where it will be paid off by) I still have principal remaining. Here is the image of how I have it set up with the formula bar visible. The payment and rate are fixed over a 60month period. In cell C1, I have "=B41-B45" with all the following cells in the row set up in the same fashion. By month 60, there is still 3150.87 leftover on the principal.

    Capture.PNG

    I tried it using PPMT instead of PMT, and the problem still remains with 4286.42 leftover. Below is that image, C1 is still the same.

    Capture2.PNG

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,238

    Re: PMT Formula Dysfunction

    I think you might have to upload the sheet if possible. This is how my payment formula looks =PMT(B43/12,60,B41,0) (though using the extra parens don't seem to make a difference) but dragged out over 60 months using your beginning numbers comes within $159.54 of the payoff value. I get similar numbers using my finance calculator.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-31-2018
    Location
    Alabama, USA
    MS-Off Ver
    MS 2016
    Posts
    3

    Re: PMT Formula Dysfunction

    Okay, I attached the document. Thanks for the help!
    Attached Files Attached Files

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,238

    Re: PMT Formula Dysfunction

    I will take a look tomorrow because my home version of excel is for the mac and it isn't as functional as my work version on PC.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,462

    Re: PMT Formula Dysfunction

    Until Sambo kid can have a look, I will offer a few observations:

    1) How much of this is Excel, and how much of this is the more generic business math problem around creating an amortization schedule? It has been a long time since I last really looked at amortization schedules, but something about yours seems wrong, because in either case the amount going to principle is decreasing over the life of the loan, where I would expect it to be increasing. I am thinking that you are misunderstanding how the PMT() and PPMT() functions are used.

    2) I think I made a breakthrough by looking at the PPMT() function. If I understand the help file correctly (https://support.office.com/en-us/art...a-b06c6ac95e1b ) the PPMT() function is supposed to provide the amount that goes to principle based on the beginning balance of the loan, the periodic interest rate, the total number of periods, and the period in question. I changed your PPMT() function from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Note the absolute column references to the interest rate and the initial loan value. When I copied this across, it appears to give a correct value for the final payment at the end of the loan. My correction is rooted in a different interpretation of the parameters of the PPMT() function (in particular, interpreting the pv parameter to mean the initial value of the loan rather than the month by month value of the loan).

    I am not a financial expert, but I think that you will find the error by checking your assumptions and understandings of how loan amortizations work, and how Excel's financial functions fit into these loan amortizations.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: PMT Formula Dysfunction

    The PMT function does not return the "payment towards principal" (A6).

    Instead, it returns the fixed payment that is needed to reduce the PV to the FV in the specified NPER number of payments at a periodic (i.e. per-payment) interest RATE in the first parameter. That fixed payment covers both interest and principal amounts for the duration of the loan or annuity.

    In contrast, the PPMT function does indeed return the "payment towards principal" for the specified period (PER).

    However, PPMT calculates the fixed payment (PMT) internally based on the function parameters. The fixed payment is not rounded, since there is no indication of how it should be rounded. (Excel does not make the assumption that the amounts are dollar-and-cents, for example.)

    So, if your actual payment is rounded, PPMT might return amounts that differ from an amortization schedule.

    However, you are misusing PPMT insofar as changing the FV, but not changing the NPER (60).

    One correct use is =-PPMT(B11/12,B12,60-B12+1,B9,0,0). That would be useful if you want allow for variable interest rates.

    However, a more common use is =-PPMT($B$11/12,B12,60,$B$9,0,0) for the fixed interest rate applied to the original principal (PV).

    PS.... There is nothing wrong with your use of B4 and B11 instead of $B4 and $B11, since you have the interest rate in C4 and C11 et al. In fact, your design allows you to have different interest rates, which might apply to a variable-rate loan or annuity.
    Last edited by joeu2004; 02-01-2018 at 12:43 PM. Reason: minor

  7. #7
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: PMT Formula Dysfunction

    For an example of one correct usage of PMT and PPMT, see the attached Excel file.

    The "unrounded" worksheet uses the unrounded return of the PMT function. In that case, there is no difference between PPMT and the periodic principal amount in the amortization schedule.

    The "rounded" worksheet rounds PMT to 2 decimal places, as we would do in the real world. In that case, there is some difference between PPMT and the periodic principal amount in the amortization schedule. Also note that the final balance is not zero, due to rounding.
    Attached Files Attached Files

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,238

    Re: PMT Formula Dysfunction

    Mr Shorty and joeu2004 seem to have given you reasonable answers / observations that I'm not sure I could add much to.
    I thought Mr Shorty's first point was what I was looking at just based on your pictures in post #1.
    Do you still have the issue or did they move you toward the solution?

  9. #9
    Registered User
    Join Date
    01-31-2018
    Location
    Alabama, USA
    MS-Off Ver
    MS 2016
    Posts
    3

    Re: PMT Formula Dysfunction

    That cleared it up for me. Thank you everyone!

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,238

    Re: PMT Formula Dysfunction

    Glad we could be of help.
    Since you are new to the forum, for general housekeeping it is good if you can mark your post as solved using the thread tools dropdown at the top of this post.
    AND it is a nice gesture if you click on the *add reputation below the posts for ANY or ALL who stopped by to help you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  2. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  3. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  4. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1