Go
to the Prior Tip The Black Swan
by Nassim Taleb
Go to the Next Tip To Forgive Design:
Understanding Failure
Return to MaxValue Home Page
In the U.S., and perhaps elsewhere, automobile advertisements often include an offer. "You can own this car for only 72 monthly payments of only $485." There is often a modest down-payment requirement. Usually, the ad will include a footnote that the offer is "for well-qualified buyers."
This calculation example was inspired by an April 9, 2013,
article in The Wall Street Journal, "Introducing the 97-month car loan"
by Mike Ramsey.
http://autos.yahoo.com/news/introducing-the-97-month-car-loan-202203365.html
If this link non longer works, you might find it by searching with words such
as: 'wsj 97 month car loan'.
A $500 monthly payment seems to be a ceiling for many car purchasers, so the dealer wants to keep payments below that amount if possible. Even with recent low interest rates, it's easy to exceed $500/mo. with inflated car prices. One way to lower the payments is to extend the number of payments. Currently, creditors are more-willing to offer favorable loan terms as default rates are low and cars have been maintaining strong resale values.
The loan interest rate is often omitted from the advertisement. Readers may be interested in the calculation of the interest rate given: Total purchase price, number of monthly payments, and amount of those month-end payments.
Be especially wary of ads promising a very low-interest rate. Often, the car price will be higher in the loan calculations than what you could buy the car for in a cash transaction. If possible, try to get the dealer to reveal both a cash purchase price and the financing purchase price. Knowing the cash (i.e., actual) price and payment terms, we can calculate the "imputed interest rate."
For this illustration, we'll assume the loan amount is the cash purchase price. One way to ensure this is for the buyer to arrange his or her own bank financing. The car dealer receives the loan proceeds.
A Microsoft® Excel spreadsheet illustrates three ways of calculating the interest rate. Download it here: http://www.maxvalue.com/CarLoanIntRate.xlsx and save it to your hard drive in a convenient folder. An older version of MS Office may require the folder be saved with an .xls extension.
Here are the base assumptions:
| Car Purchase Price = Loan Amount = present value (PV) | $31,000 |
| Monthly Payment Amount (Pmt) (payments at month-ends) | $460 |
| Number of Monthly Payments (n) | 75 |
I'm still using my trusty H-P 12C calculator that I bought in the 1980s. This calculator model is still popular, though they've been redesigned inside several times. On this and other financial calculators are five key buttons. <n> number of periods; <i> interest rate; <PV> present value; <PMT> periodic payment (annuity); and <FV> future value. Entering values for any three variables allows calculation of either of the other two. At the upper-right of the spreadsheet is the key sequence for solving this example. The interest rate is about 0.0028698/mo. This translates into (1+.0028698)^12–1 = 0.0350/yr. = 3.50%/yr. This is the "APR" (Annual Percentage Rate) that is required in the U.S. for loan term disclosures.
I searched the Internet for auto loan rates and found a website that would display loan rates by state and FICO score tiers. FICO scores are widely used in the U.S. as an index of credit-worthiness. The 3.5% rate fits between the rates reported for the top two tiers.
Excel has functions similar to the financial calculators. For calculating the monthly interest rate we can use (shown at the upper-center part of the spreadsheet):
i = Rate(n,Pmt,-PV) = Rate(75,460,-31000) = 0.28698%/mo. As shown in the compounding equation, above, this is 3.50%/yr.
This next method is more general and transparent. Payment amounts and time intervals can vary. What we're solving for is the internal rate of return (IRR).
IRR is the interest rate that makes PV = $0.
At the right side of the spreadsheet is a loan amortization schedule. It is set up for 75 month-end payments of $460 each. Each row calculates the monthly interest on the unpaid loan principal at month-start. $460 minus the interest payment equals the principal payment. At the end of month 75, we want to have at $0 balance. [I did not round the amounts to cents, so an actual loan schedule would have some slight variations due to rounding.]
It is good practice to put a reasonable estimate for i in the A9 cell. Sometimes there can be multiple solutions to IRR though this simple cashflow schedule will not have a multiple-roots problem. Use Excel's Goal Seek function. In Excel 2010 you get there by Data tab > What-If Analysis > Goal Seek. Set the end balance (cell O85) to $0 by changing i cell A9. As with the other methods, we also get .28698%/mo.
In this example, we're assuming end-period payments. That's often the assumption in simple software and typical textbooks. When calculating PVs and IRRs for a real project, one should pay attention to the time periods in order to calculate correctly. For example, with yearly cash flows we typically have the the amounts received more-or-less uniformly during the year. So mid-year discounting is a common assumption in evaluation models. The NPV() function in Excel does not do this correctly, though the half-year correction is easy if there are 12-months in each year period.
—John Schuyler, Apr 2013
Copyright © 2013 by John R. Schuyler. All rights reserved. Permission to copy with reproduction of this notice.