Future value finance question

[Nycginger]

For those of you who understand finance/future value, if I have $406,000 invested at 4% (.003333333 a month) over 384 months (32 years), adding 0 on a regular basis, and subtracting $2000 for living expenses on a monthly basis, then the formula looks like this in excel: =fv(.003333,384,0,-$406,000)-(2000*384). In excel is comes out to $689,130.77.

But then if I change it to 10 years or 120 months the overall amount drops to $385,278. I realize that 4% of the original $406,000 is $16,240, and that I’m spending on average $24,000 a year, but I wonder how this works, why it works this way, and why the number eventually climbs higher?

I have some other assets I’m not including in the $406,000 number, but just trying to understand if I can FIRE.

Thanks!

[Mrs. PoP]

You're using the formula wrong.   The $2000 in monthly expenses should be as a negative "addition" in the formula instead of just adding 0 every month.   Think of it this way - The formula you have now says let this money grow for 32 years (the fv part), then remove an average of 24K for each year in one lump sum at the end of the 32 years (the subtracted part).  But after that long, the average dollar growth was more than 24K per year thanks to compounding, so it's giving you a skewed picture.

You (I assume) want to be removing 2000 per month, or 24K per year, throughout those 32 years so that needs to go into your formula instead of the zero.  Not having excel open in front of me, I can't tell if it needs to be -2000, or +2000, but try it with 1 Month first and you'll be able to tell which one it is. 

HTH!

[MDM]

...just trying to understand if I can FIRE.

As posed, the answer is "no".

See the 'Misc. calcs' tab of the case study spreadsheet, pictured below.



For the numbers given, one would go ~$100K into the hole (depending on whether the $2K/mo was removed at the end or start of each month).
One could last ~28 years before running out of money.
Or make the full 32 years either by withdrawing $1876/mo, or having ~4.5% ROI.

[maizefolk]

Mrs. PoP is correct, your formula isn't taking out your spending as it occurs but assuming it all continues to compound to the end of the time period and THEN withdrawing the money.

However, there is another, smaller, problem with your formula as well. If your income grows 0.3333333%/month, then your assumed annual growth rate is (1.0033333333^12)-1 which works out to an annual growth rate of about 4.07% not 4% through the magic of compounding.

The monthly interest rate which produces an annual return of 4% is about 0.32737%