Question

USE EXCEL

You’re bored at work and so want to figure out how long until you can retire. Suppose that you have $700,000 in savings today (today is 1-Jan-2021). This savings account earns a 3% return each year. Specifically, the interest accrues each day at a daily rate of 0.03/360, and the interest compounds annually (i.e. the interest gets added to the interest-earning principal at the end of each year). From your salary, you will contribute $22,000 in additional savings at the end of each year, starting one year from today. You calculate that once these savings reach or exceed $1 million, you will retire. (For this problem, I suggest you use Excel.)

1. How many years and days will it take for you to have enough savings (including accrued interest) for you to retire?

2. You just spent $1000 today on a ticket to Hawaii. If you hadn’t, you would have started with $701,000 in savings. How much earlier could you retire if you had not bought this ticket?

Answer 1

Let's find the time taken to reach $ 1 million = Nper (Rate, PMT, PV, FV) = Nper (3%, 22000, 700000, -1000000) = 6.429340449

So, it's going to take 6 years and a few more days. Let's say it takes N days beyond year 6 to reach 1 million mark.

FV in exact 6 years = FV (Rate, Nper, PMT, PV) = FV (3%, 6, 22000, 700000) = 978,141.63

Amount short by 1,000,000 - 978,141.63 = 21,858.37

Accumulated interest over N days should bridge this gap.

Hence, FV x N x daily rate = 21,858.37

Or, 978,141.63 x N x 0.03/360 = 21,858.37

Hence, N = 268.16

Hence, it will take for you 6 years and 268 days to have enough savings.

-----------------------

Let's find the time taken to reach $ 1 million = Nper (Rate, PMT, PV, FV) = Nper (3%, 22000, 701000, -1000000) = 6.41

So, it's going to take 6 years and a few more days. Let's say it takes N days beyond year 6 to reach 1 million mark.

FV in exact 6 years = FV (Rate, Nper, PMT, PV) = FV (3%, 6, 22000, 701000) = 979,335.68

Amount short by 1,000,000 - 979,335.68 = 20,664.32

Accumulated interest over N days should bridge this gap.

Hence, FV x N x daily rate = 20,664.32

Or, 979,335.68 x N x 0.03/360 = 20,664.32

Hence, N = 253.20

So, you could have retired earlier by 268 - 253 = 15 days