Typical Financial Planning Issues –
Example 1
• You want to save $20,000 for a down payment on a new
house in five years. You can earn 7% on your money.
How much would you need to save each month in order
to reach your goal?
• FV = 20,000, n = 5 years, i = 7% per year and we want
to solve for PMT
Calculator Entry Calculator Display
Step 1 20000 FV 20,000.00
Step 2 7gi 0.58
Step 3 5gn 60.00
Step 4 PMT -279.36
Typical Financial Planning Issues –
Example 2
• You will have $20,000 in student loans upon
graduation and would like to pay them off in 8 years.
The interest rate is 8%. How much do you need to pay
each month?
• PV = 20,000, n = 8 years, i = 8% per year and we want
to solve for PMT
Calculator Entry Calculator Display
Step 1 20000 PV 20,000.00
Step 2 8gi 0.67
Step 3 8gn 96.00
Step 4 PMT -282.73
1
Typical Financial Planning Issues –
Example 3
• Because auto insurance rates are very high for those
under age 25, you must pay $2000 a year to insure
your car. If you didn’t own a car for the next five years
and can invest the money at 7%, how much would you
accumulate by not having to purchase auto insurance?
• PMT = 2000, n = 5, i = 7% and we want to solve for FV
Calculator Entry Calculator Display
Step 1 2000 CHS PMT -2000.00
Step 2 7i 7.00
Step 3 5n 5.00
Step 4 FV 11,501.48
Typical Financial Planning Issues –
Example 4
• You can’t afford to purchase a big screen TV today for
$1300, but you can afford to pay $85 a month to rent
one from a rent-to-own facility. If you make your
payments for the next 3 years, the TV is yours to keep.
What is the stated annual interest rate implied by this
arrangement?
• PV = 1300, PMT = 85 per month, n = 3 years and we
want to solve for i
Calculator Entry Calculator Display
Step 1 1300 PV 1300.00
Step 2 85 CHS PMT -85.00
Step 3 3gn 36.00
Step 4 i 5.63
Step 5 12 x 67.53
2
Future Value In Excel
A B C D E F G
1 What is the future value of $1,000 at the end of 3 years at a 10%
2 interest rate?
3
4 FV(rate,nper,pmt,pv)
5
6 Present value (pv): $1,000.00
7 Periodic payment (pmt) 0
8 Number of periods (nper) 3
9 Interest rate (rate) 10%
10
11 Future value: $1,331.00
12
13 The formula entered for future value is =fv(c9,c8,-c7,-c6)
Monthly Payment In Excel
A B C D E F
1 What is the monthly payment needed to pay off a $20,000 student
2 loan in 8 years at an 8% interest rate?
3
4 Pmt(rate,nper,pv,fv)
5
6 Future value (fv): $0.00
7 Present value (pv) $20,000.00
8 Number of periods (nper) 8
9 Interest rate (rate) 8%
10
11 Periodic payment (pmt) $282.73
12
13 The formula entered for periodic payment is =-pmt(c9/12,c8*12,c7,c6)
3
Interest Rate in Excel
A B C D E F G
1 What is the interest rate on a $20,000 8-year loan on which the
2 monthly payments are $282.73
3
4 rate(nper,pmt,pv,fv)
5
6 Future value (fv): $0.00
7 Present value (pv) $20,000.00
8 Periodic payment (pmt) 282.73
9 Number of periods (nper) 8
10
11 Interest rate (rate) 8%
12
13 The formula entered for interest rate is =12*rate(c9*12,-c8,c7,c6)
Discussion Case
Doug Klock, 56, just retired after 31 years of teaching. He is a
husband and father of three children, two of whom are still
dependent. He received a $150,000 lump-sum retirement bonus
and will receive $2,800 per month from his retirement annuity. He
has saved $150,000 in a 403(b) retirement plan and another
$100,000 in other accounts. His 403(b) plan is invested in mutual
funds, but most of his other investments are in bank accounts
earning 2 or 3 percent annually.
Doug has asked your advice in deciding where to invest his lump-
sum bonus and other accounts now that he has retired. He also
wants to know how much he can withdraw per month considering
he has two children in college and a nonworking spouse.
Because Rachel and Ronda are still in college, his current monthly
expenses total $5,800. He does not intend to begin receiving
Social Security until age 67 and his monthly benefit will amount to
$1,550. He has grown accustomed to some risk but wants most of
his money in FDIC-insured accounts.
4
Discussion Case
Question 1.
Assuming Doug has another account set aside for emergences,
how much can he withdraw on a monthly basis to supplement his
retirement annuity if his investments return 5 percent annually and
he expect to live 30 more years?
The approximate monthly payment using the calculator is
$2,147.29.
PV -$400,000
PMT ?
I/Y 5%/12=.4167%
N 30*12=360
FV 0
CPT PMT $2,147.29
Discussion Case
Question 2.
Ignoring his Social Security benefit, is the amount determined in question
1 sufficient to meet his current monthly expenses? If not, how long will
his retirement last if his current expenses remain at $5,800 per month? If
his expenses are reduced to $4,500 per month?
His retirement income will last for 195 months (16.3 years) or until
approximately age 73. His portfolio needs to generate $3,000 in income
per month ($5,800 – $2,800 retirement annuity).
PV -$400,000
PMT $3,000
I/Y 5%/12=.4167%
N ?
FV 0
CPT N ≈ 195 months
5
Discussion Case
Question 2.
Ignoring his Social Security benefit, is the amount determined in question
1 sufficient to meet his current monthly expenses? If not, how long will
his retirement last if his current expenses remain at $5,800 per month? If
his expenses are reduced to $4,500 per month?
Now His portfolio only needs to cover $1,700 per month ($4,500 –
$2,800). His retirement income will last for 945 months (78.8 years) or
until approximately age 135.
PV -$400,000
PMT $1,700
I/Y 5%
N ?
FV 0
CPT N = 945 months
Discussion Case
Question 3.
If he withdraw $3,000 per month, how much will he have in 11
years when he turns 67?
Factoring in the Social Security benefit, and using his current
projected expense level of $5,800 he will deplete his portfolio at
age 80.
The value of the portfolio remaining at age 67 is $165,992.44
PV -$400,000
PMT $3,000
I/Y 5%/12=.4167%
N 11*12=132
FV ?
CPT FV $165,992.44
6
Discussion Case
Question 3.
If he begins to receive Social Security payments of $1,550 at 67,
how many years can he continue to withdraw $1,450 per month
from his investments?
Step 2: Using the future value from above as the new present
value calculates that Doug’s portfolio will last an additional 156
months or 13 years.
PV -$165,992.44
PMT $1,450
I/Y 5%/12=.4167%
N ?
FV 0
CPT N ≈ 156 months
Discussion Case
Question 4.
If the inflation rate average 3 percent during Doug’s retirement, how old
will he be when prices have doubled from current levels? How much will a
soda cost when Doug dies, if he lives the full 30 years and the soda costs
$1 today?
Yes, prices will double by the time he is 80 years old. Using the
Rule of 72 you determine that, at 3% inflation, it takes 24 years for
prices to double. The can of soda will cost $2.43 in 30 years
assuming 3% annual inflation.
Factor Table A solution Calculator solution
PV $1 PV -$1
PM T n/a PMT 0
(FVIF3%,30) 2.427 I/Y 3%
N 30
FV $2.43 FV ?
CPT FV $2.43
