Understanding the Mathematics of Personal Finance
A VERY INVOLVED EXAMPLE: WRITING YOUR OWN SPREADSHEET
As a last example, I’ll present a calculation that probably can’t be done easily using online calculators. In working through it on a spreadsheet, I’ll also be able to easily extend our mathematical notation and take advantage of some spreadsheet mathematical capabilities that have not been introduced yet. If you are not comfortable with creating your own spreadsheets and don’t want to venture down this path, you can skip this section.
Assume that you have access to an equity line of credit. That is, you have a checkbook that lets you write yourself loans that are backed by your equity in your home.
You want to buy a car on January 1 of the upcoming year, and you want to fund it 100% with a home equity loan. Your bank interest rate on this loan will be 8%, compounded monthly. Looking at your budget, you are pretty sure that you can make $250 a month payments. You want to fully pay off this loan in 4 years. Based on your work history, you’re very confident that each year you will get a raise in pay, so that in the second year your can pay $300 a month; in the third year, $350 a
month; and in the fourth year, $400 a month. You’d like to plan on several other variations; you usually spend a lot on presents during the holiday season in December, so you want to be able to skip January payments. Each year you get a tax refund sometime in May, so on June 1 you can add $1,000 to your regular payment. Right around Thanksgiving, your boss hands out bonuses, and you’re confident that each December 1 you will be able to add $500 to your regular payment.
What color car should you buy?
Just kidding. The real question is how much of a loan can you take?
Table 7.4 shows a spreadsheet for working out this problem. The cell column numbers, on the left, are identically the month numbers for the loan. Row A shows the payment schedule. Row A must be entered by hand because the payments are the odd collection of numbers that I generated. If you add up all the payments, you get $20,300.00. At this point, you should already know that the present value of all these payments on day 1, which is the amount you can finance, will not be $20,300.00.
In column B, I want to put the amount that each of the payments in column A must be divided by to get the correct present value.
Call the monthly interest rate r. The present value of $250 paid 1 month from now is then
$250 (1 + r).
The present value of $250 paid 2 months from now is just the present value of $250 paid 1 month from now, divided again by (1 + r),
$250
(1 + r Г and then the present value of a $250 payment 10 months from now would just be written as
$250 (1 + r )10.
Spreadsheet notation is a bit different from textbook notation. Rather than typing = (1 + r)10, in the spreadsheet you would type = (1 + г)л10. It means exactly the same thing.
As an example, look at the tenth payment (row 10 in the spreadsheet). This is a $250 payment, so you would want to calculate 250/(1 + г)л10. This would work just fine, but if you do it this way, you have to type exactly what you want for each of the 48 payments. Instead, let’s make full use of the power of the spreadsheet.
There is a spreadsheet function called “row” that returns the row of a selected cell. Let’ s say we want to put the number you divide the payment by to get the correct present value into column B. This calculation for the tenth payment would belong next to cell A10, the amount of the tenth payment, in cell B10. Working in cell B10, the function row(A10) returns the row that cell A10 is in (10). However, the tenth payment occurs 9 months after the start of the loan, so we need to enter row(A10) - 1.
Now let’ s put this all together. Remember that for an 8% annual loan, the monthly interest rate is 0.08/12, so in cell B10 you enter = (1 + 0.08/12)-'(row(A10) - 1). If everything is correct, cell B10 should now say 1.062. Copy this formula up and down the entire B column (all 48 entries).
In row C, you’ll put the present value of all the payments. Therefore, in C1, enter {=A1/B1} and copy this down row C for all 48 entries.
The number we’re looking for, the sum of all 48 payments, is simply the sum of all of the entries in row C. Pick an empty cell and enter = sum(c1:c48). The term “sum” is another spreadsheet function. It simply adds up all the entries. The notation c1:c48 means “add up all the entries between c1 and c48.” Your result should be $17,181.72.
A total of $17,181.72 is the amount you can borrow from your equity loan account and, if you meet the payment schedule you entered on the spreadsheet, after all 48 payments are made, the loan will be exactly paid off.
