# PMT Formula Dysfunction

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

3. ## Re: PMT Formula Dysfunction

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

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

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

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

8. ## 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. ## Re: PMT Formula Dysfunction

That cleared it up for me. Thank you everyone!

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

##### Users Browsing this Thread

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

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