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 mathe­matical 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

Подпись: PV1 =$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),

Подпись: PV2 =$250

(1 + r Г and then the present value of a $250 payment 10 months from now would just be written as

Подпись: PV10 =$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.

Добавить комментарий

Understanding the Mathematics of Personal Finance

PAYING OFF A LOAN VERY SLOWLY

This section uses a little math, but I’ll go through it slowly in small steps. As with the previous section, this section is not necessary if you don’t want to …

Taxation and Inflation

John Lennon once said, “Life is what happens to you while you’re busy making other plans.” Both taxation and inflation, it seems, are parts of life—they happen to you while …

PARI-MUTUEL BETTING

The pari-mutuel machine system used at race tracks offers a different approach to gambling. A roulette wheel owner is never sure what his or her daily operating cost will be; …

Как с нами связаться:

Украина:
г.Александрия
тел./факс +38 05235  77193 Бухгалтерия
+38 050 512 11 94 — гл. инженер-менеджер (продажи всего оборудования)

+38 050 457 13 30 — Рашид - продажи новинок
e-mail: msd@msd.com.ua
Схема проезда к производственному офису:
Схема проезда к МСД

Партнеры МСД

Контакты для заказов шлакоблочного оборудования:

+38 096 992 9559 Инна (вайбер, вацап, телеграм)
Эл. почта: inna@msd.com.ua

За услуги или товары возможен прием платежей Онпай: Платежи ОнПай