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
to
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.
Bookmarks