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.

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.

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.

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.

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?

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.