One of my many financial dreams is to someday be able to purchase a nice coastal cruising “yacht” like the Hinckley Picnic Boat shown in the picture above. At a cool $250,000 for a 10 year old model, the reality of me ever owning one and affording the subsequent maintenance, storage, and operating expenses is pretty darn unrealistic. However, its still fun to dream about…
In today’s personal finance lesson, I want to explore the “math” behind saving for a large midlife crisis purchase like a picnic boat, exotic car, or maybe even a vacation property. In true Trees Full of Money fashion, I also want to use this as an opportunity to futher explore the time value of money and the opportunity cost of capital as it relates to your personal finances.
Example: Let’s say that my financial goal was to save enough money in 10 years to be able to buy a 10 year old Hinckley Picnic Boat. Let’s also assume that I’m taking the conservative route and investing the money in relatively safe investment grade bonds paying an average annual interest rate of 6%. Assuming the boat will cost $250,000 in “today’s” dollars, how much will I need to save per year to buy this killer boat outright?
In this example we are given how much money in today’s dollars we need to save in the next 10 years ($250,000).
We are also given the interest rate that our yearly savings will be invested at (6%).
What we’re looking for here is how much money per year we need to save (assuming we save the same amount each year).
Because we are saving the same amount of money each year (for 10 years), our savings can be considered an annuity (annuity is just a fancy financial term for a set payment issued over a certain time period). Therefore, we can use the present value of annuity formula I first mentioned a few weeks ago in my article on how to calculate how much money you need for retirement.
Here is the formula for the present value of an annuity factor:
you can use the “Present Value of an Annuity” formula to find the “annuity factor”. Once you find the annuity factor you can multiply it by your desired annual income in retirement to find how much you will need to have saved:
PV Annuity Factor = [(1/r)-(1/r(1 + r)^t)]
PV = How much money you will need in “today’s dollars” on your target retirement date (in our example we need $2,000 per month or $24,000 per year).
r = The rate of return on your savings investments (6% in our example).
t = The number of years you have to save (10 years in our example)
Looking at our example above, let’s plug our variables into the Present Value of an annuity formula using Excel and see what happens…
(1/0.06)-(1/((0.06)*((1+0.06)^10))) = 7.36
Perhaps you’re wondering why this number is so small? Remember, this number is just the “annuity factor” with which we need to divide the Present value of the $250,000 in the future.
To find the present value of what $250,000 is worth 10 years from now, we divide $250,000 by 1.06^10:
250000/1.06^10 = $139,598 (in other words, if you invested $139,598 today at 6%, it would be worth $250,000 ten years from now).
Now we divide the present value of $250,000 ten years from now ($139,598) by our annuity factor (7.36) to determine how much we need to save each of the next 10 years to have $250,000 to make our midlife crisis purchase.
139598/7.36 = $18,967 per year (doesn’t look like my dream is going to be happening!!!)
We can divide this number by 12 to determine how much we would need to save per month (at 6% interest) to save $250,000 towards our midlife crisis.
18967/12 = $1580 per month (still a little rich for my blood!!!)
What about inflation???
Remember, I mentioned that I wanted to buy a boat in the future that was comparable to what $250,000 would buy today. For historical purpose, lets assume a 3% rate of inflation (about the average over the last 110 years).
This means our 6% rate of return is no longer applicable. Instead we need to calculate our “real” rate of return which takes the effects of inflation into consideration.
To find the real rate of return we use the following formula:
(1 + Real Rate of Return) = (1+ Nominal Rate of Return)/(1 + Inflation Rate)
In our example the Real Rate of Return = 1.06/1.03 = 1.029 – 1 = .029 or 2.9%
Now that we have our real rate of return, we can plug that back into the above calculations to get our new annuity factor:
(1/0.029)-(1/((0.029)*((1+0.029)^10))) = 8.57
We also need to recalculate what the present value of $250,000 is worth based on a real rate of return of 2.9%:
250000/1.029^10 = $187,839
Now we divide the “new” present value of $250,000 ten years from now ($187,839) by our “new” annuity factor (8.57) to determine how much we need to save each of the next 10 years to have $250,000 of today’s dollars to make our midlife crisis purchase.
187839/8.57 = $21,918 per year or $1,826 per month (YIKES)
There goes my midlife crisis dream (damn financial formulas, they always ruin everything)! Oh well, I guess that’s what credit cards, boat loans, HELOC’s, and mortgages are for! Just kidding or course!
Of course there is a shortcut for all of this silliness, and I’ve saved the best for last. Instead of wasting all of your time with the above formulas and manually typing them into Excel (or even worse using a calculator), there is a really easy application hidden within MS Excel that will calculate how much you need to save automatically!
Simply open a new Excel workbook and click in any blank cell.
Next click on the function key (circled in the picture below).
A pop-up window will appear from which you can select different functions which are built into Excel.
For our calculation, select the “financial” category and then select the PMT (or payment) function as I have in the picture below.
Once you have done this click “OK” and you’ll get another window to pop up where you can enter your values:
In the above example, I have decided to go with the real rate of return (considering inflation) using the interest rate of 2.9% (you can enter 2.9% or simply .029 if you want to enter it numerically in Excel).
Nper: Is simply the number of periods we have until our target date (10 periods or years in our case).
Since we want $250,000 in the future, we enter 250000 in the “FV” or future value box.
That’s it! You’ll see the “payment” or yearly contribution requirement circled above that represents how much we need to “pay” our savings account each year to have $250,000 at the end of 10 years. Note that the payment -21908.25 is negative because it is money we are “paying-out”. You’ll also note the payment is slightly different from what we manually calculated above, this is simply do to more precise rounding done by Excel.
That’s it folks! All you ever wanted to know about funding your midlife crisis! With all of the said though, what fun is a midlife crisis if you take away all of the spontaneity. Happy saving!
Too Much Debt? Download our free Trees Full of Money Debt Snowball Calculator and see how quickly you can pay off your debt.