### Author Topic: Help correcting a formula on my spreadsheet to forecast FI date  (Read 1641 times)

#### moustacheverte

• Stubble
• Posts: 145
• Age: 35
• Location: Switzerland
##### Help correcting a formula on my spreadsheet to forecast FI date
« on: November 29, 2015, 03:41:42 PM »
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

• Senior Mustachian
• Posts: 10666
##### Re: Help correcting a formula on my spreadsheet to forecast FI date
« Reply #1 on: November 29, 2015, 05:01:51 PM »
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

• Pencil Stache
• Posts: 548
##### Re: Help correcting a formula on my spreadsheet to forecast FI date
« Reply #2 on: November 30, 2015, 08:51:55 AM »
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))

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

• Stubble
• Posts: 145
• Age: 35
• Location: Switzerland
##### Re: Help correcting a formula on my spreadsheet to forecast FI date
« Reply #3 on: December 06, 2015, 11:14:03 AM »
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.