Help correcting a formula on my spreadsheet to forecast FI date

[moustacheverte]

Hello,

I have a spreadsheet where I calculate my potential FI date based on the average monthly growth of my portfolio (appreciation + new deposits).

I was using the dumbest formula: ((yearly spending * 25) - (saved sum / average monthly appreciation in dollars)) / 12 and this would give me how many months I have left. I am completely overlooking compound interest and when I put my numbers into calculator soup, I'm getting a date 4 years earlier (a third earlier!)

I got lost with the huge formulas required and I'm not too hot with logarithms etc. Is there a straight-forward formula I could use to calculate when I'll hit my FI objective given a 7.2% yearly rate, 12 payments per year, interest compounding 12 times a year end of period, current monthly payments and the already saved sum?

Thanks,

[MDM]

You could check cells E14 through E20, or C44 through C49, on the 'Misc. calcs' tab in the case study spreadsheet: http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-%27case-study%27-topic/msg274228/#msg274228.

Does that give you what you seek?

[shotgunwilly]

Future Value of an Annuity Formula:

FV = PV(1 + i)^n + [ R ( (1 + i)^n - 1 ) ] / i

FV = Future Value
PV = Present Value (Current amount invested)
i = Monthly estimated return (Yearly estimated return divided by 12. I use 7/12, or .0058)
n = Number of compounding periods (for this example it's (Years until retirement) x (12 months))
R = The amount per month you're adding to your investments from your income


You will have to format it slightly different in excel and make sure all of your parenthesis are in the right location so that it will work.  This will give a good rough estimate of the future value you may have when you plug in __ years until retirement.

I would then just make a chart or maybe just a sheet that has each year and the Future Value next to it so you can see what they may look like.

[moustacheverte]

You could check cells E14 through E20, or C44 through C49, on the 'Misc. calcs' tab in the case study spreadsheet: http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-%27case-study%27-topic/msg274228/#msg274228.

Does that give you what you seek?

Yes, thank you. There is a function in google sheets actually, I saw it by importing the spreadsheet you linked into it. It's called NPER.