Question

• GIVEN: Francis Frugal has just turned 65 and is about to retire. She has $1,500,000 in her bank account and plans to live on $76,500 per year. Please show the equations. Not on Excel.

A.    If she lives to age 95, what is the minimum rate of return she needs to earn during retirement?

B.   Oh no! Her three bachelor sons all suddenly lost their jobs and have to move back in with her. Poor Francis will have to spend $172,500 per year supporting herself and her three sons! What is the minimum rate of return she will now need to earn during retirement

C.   Assuming the same discount rate of return in part B., what single (lump-sum) amount would Francis have needed at age 25 to support her entire family when she turned 65?

Answer 1

Part A

Particulars	Amount
PV Annuity	$ 1,500,000.00
Time Period	30.00
Cash Flow	$       76,500.00

PV of Annuity = Cash flow * PVAF(r%, n)
PVAF(r%, n ) = PV of Annuity / Cash Flow
= $ 1500000 / $ 76500
= 19.6078

PVAF = SUm [ PVF(r%, n) ]
PVF(r%, n) = 1 / ( 1 + r)^n
r = Int rate per period
n = No. of periods

or PVAF = [ 1 - [(1+r)^-n]] /r

How to calculate PVAF using Excel:
=PV(Rate,NPER,-1)
Rate = Disc Rate
NPER = No.of periods

   Trial & Error Method
Low Rate   2.50%
High Rate   3.50%

The Rate at which PVAF for 30 Periods will be equal to 19.6078 will be the answer.
PVAF(2.5%30) = 20.9303
PVAF(3.5%30) = 18.392

Required Rate = 2.5 % + [ [ 20.9303 - 19.6078 ] / [ 20.9303 - 18.392 ] ] * 1 %
= 2.5 % + [ [ 1.3225 ] / [ 2.5383 ] ] * 1 %
= 2.5 % + [ 0.521 ] * 1 %
= 2.5 % + 0.521 %
= 3.021 %

Part B

Particulars	Amount
PV Annuity	$ 1,500,000.00
Time Period	30.00
Cash Flow	$     172,500.00

PV of Annuity = Cash flow * PVAF(r%, n)
PVAF(r%, n ) = PV of Annuity / Cash Flow
= $ 1500000 / $ 172500
= 8.6957

PVAF = SUm [ PVF(r%, n) ]
PVF(r%, n) = 1 / ( 1 + r)^n
r = Int rate per period
n = No. of periods

How to calculate PVAF using Excel:
=PV(Rate,NPER,-1)
Rate = Disc Rate
NPER = No.of periods

   Trial & Error Method
Low Rate   10.50%
High Rate   11.50%

The Rate at which PVAF for 30 Periods will be equal to 8.6957 will be the answer.
PVAF(10.5%30) = 9.0474
PVAF(11.5%30) = 8.3637

Required Rate = 10.5 % + [ [ 9.0474 - 8.6957 ] / [ 9.0474 - 8.3637 ] ] * 1 %
= 10.5 % + [ [ 0.3517 ] / [ 0.6837 ] ] * 1 %
= 10.5 % + [ 0.5144 ] * 1 %
= 10.5 % + 0.5144 %
= 11.0144 %

Part C

interest rate r = 11.01 %

n = 65 - 25 = 40 years

present value at the age of 25 years = future value * future value factor

future value factor = 1/(1+r)^n

Lump sum amount would francis ha ve at the age of 25

= $ 1500000 * 1/(1.1101)^40

= $ 1500000 / 65.2355

= $ $ 22993.61

if francis had $ 22993.61 at the age of 25 she can reach the amount $ 1500000 at the age of 65

please comment if any further assistance is required