Last week Biomass Rules posted about comparing 4-year loans to 7-loans.  The Wall Street Journal published an article that discussed negative equity from partially paid car loans.  There are multiple factors to consider with more than one correct solution.  The key is knowing what the tradeoffs are for your situation.

This week’s, What the Function post looks at one system of MS Excel functions to use to accomplish setting up a loan schedule for a 7-year/84-month loan at 6% for a principal amount of $10,000.  The three functions discussed are:

1. The Payment Function, PMT().  This calculates the payment required for each period.  The default setting is years, but it can be easily adjusted to months.
2. The Principal Payment Function, PPMT().  This is a per-period calculation that identifies the principal portion of the payment per-period.
3. The Interest Payment Function, IPMT().  This is a per-period calculation that identifies the interest portion of the payment per-period.  The dynamic principal and interest portions sum to the constant monthly payment.

Monthly Payment Calculation, PMT()

The one-stop function here is the PMT() function.  This gives the level of payment in a single cell for multiple loan schedules. The syntax for this function inside the parentheses, is (interest rate, term of loan – number of periods, principal – present value).  There are some optional switches, but they are fairly specialized.

In today’s image, the interest rate is in Cell B1 (blue).  In the cell formula in Cell B5 (below the spreadsheet image), the ‘$’ sign anchor is used to direct the formula to always look for the interest rate in Cell B1.  Remember, Dr. Jenner Loves $, because they keep the relative position of the referenced cell from moving down the table.  The dark lines below each dollar sign in the image highlight each dollar sign.  In this PMT() formula each row and column reference includes a dollar sign anchor.

The goal in nearly every one of Dr. Jenner’s examples is to write a unique formula in a single cell and copy it to the rest of the table.  This is why a working knowledge of the $-sign row and column anchors is valuable.

The other item of note is the conversion of years to months.  The first variable is interest so the annual interest rate in B1, 6%, is divided by 12 months.  Also, the second variable, number of periods, B3, must be multiplied by 12 months to keep the monthly interest value back to the annual term of loan value.

Excel delivers the values as negative values, formatted in this example to be red inside parentheses.  If this format of negative values is annoying, there are other options in the cell formatting menu.

A very helpful application using PMT() in deciding one’s options is to create a table with interest rates across the top (2% through 10%) and Loan terms in the first column (2 through 7 years).  Then with the PMT() formula in the upper left most cell, the formula provided here would be entered.  The only difference is that the term variable in the first column would only have a $-sign on the Column address (not the row address).  The interest rate variable in the formula would only have a $-sign anchor in the Row address (not the column address).  With the $ in the correct place this table would show the monthly payments over a wide variety of loan periods and interest rates.  Powerful, right?

Monthly Principal Payment Calculation, PPMT()

These next two functions require the specific payment order to be identified.  In column A contains the values 1 through 5, in rows 5 though 9.  These represent the first 5 months of the 84-month loan schedule.  The table continues all the way to month 84.

A quick comparison of the change from the PMT() formula to the PPMT() formula illustrates simply the addition of the payment number (Column A).  For Cell C5 (upper right side of image), the payment number is the first payment, “1”, in Cell A5.  This is the only variable in these functions that does not have a $-sign anchor.  The same is true for the IPMT().  The other three variables include dollar signs and reference the same ones in the PMT() formula above.

Monthly Interest Payment Calculation, IPMT()

The only difference between the IPMT() function and the PPMT() function is the first letter of the function (“PPMT” to “IPMT”).  Well, and this formula is in Cell D5 instead of Cell C5.

It is important to look at the resulting difference between principal and interest payments to understand that the interest is paid faster than the principal.  In the original article, loan schedules of 4 years/48 months were compared with those of 7 years/84 months.  The 7-year loan can be paid back at the 4 year schedule and it will cost about the same interest as the 4-year loan would have cost.

With these three formulas in place, simply highlight these three cells and copy to the last value of 84 months.  Then paste.

Validation Checks

The benefits of copying powerful cell formulas to hundreds and thousands of cells, is only a benefit if they work.  Effective spreadsheet management requires lots of validation checks.  They can be formal or informal.  Once these three cell formulas are copied to all months in the loan schedule it is time to validate that it worked.

• First is the visual check: Are there any unusual error codes, zeroes in inappropriate places, or blank cells?  If so, look for reasons why this happened.
• Another very simple check is to sum the PPMT() column.  If all the principal payments are in order the sum of these payments should sum to the same value in Cell B2, “$10,000”.
• An often unrealized benefit are the summary statistics in the lower right corner of the worksheet.  These are identified in the “Bonus Tip: Summary Stats” in the post image.  When Cells C5 and D5 are highlighted together (the principal portion + interest portion), they sum to the payment, “$146.09”.  This screen shot was taken with those two cells highlighted.  The on-the-fly statistics provided by Excel validate those two cells do sum to $146.09.  Also provided, but not important here, are the Average and the Count.

The lesson in the 4-year/7-year loan comparison last week was ‘Pay attention to the costs of borrowed money’.  These complex, but easy to execute functions provide some valuable tools to facilitate that.

Have fun with your borrowed funds and change the world, …but pay attention!