Using FV,PV, and PMT for retirement tracking

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Keepcalm
Posts: 96
Joined: Sat May 13, 2017 7:51 pm

Using FV,PV, and PMT for retirement tracking

Post by Keepcalm » Sun Jun 18, 2017 6:40 am

I had a thread open before and branched into this question in order to save a post, however I was not receiving much response I believe due to thread title.

I do have some feedback on using these compounding excel formulas to track my retirement progress and to make sure in general I'm staying on track. However it seems to be a mixed grey area on if this is an acceptable method.

I like the idea of being independent from online calculators. I want to conceive my own tracking within MS Excel, since I recently converted from strict budgeting/tracking using YNAB and Quicken to now just tracking monthly net worth within MS Excel. After revolving my finances around a program I was never fully satisfied paying for, it's liberating being able to have my own tracking.

Paying myself first has allowed me to cut back on budgeting and I now have an inflow comfy enough to [for now] scrap such a tedious task.

Moving on, I would like to ask once more the general consensus from this community on using these excel functions for a retirement guide. To account for inflation I have based everything off a 4% return which seemed like a rational/conservative number to go off as as a net gain.

Is a historical based calculator absolute necessary or am I ok using FV?

Thanks.

carolinaman
Posts: 2502
Joined: Wed Dec 28, 2011 9:56 am
Location: North Carolina

Re: Using FV,PV, and PMT for retirement tracking

Post by carolinaman » Sun Jun 18, 2017 6:51 am

I have used these functions in Excel for retirement planning. Whenever I did, I also setup linear spreadsheets to verify results and also see progression on a year by year basis. I could quickly change variables in the spreadsheet (investment return, inflation, savings) to see the impact. I always want to cross check my calculations, and using combination of math functions and spreadsheets was good. I used spreadsheets for both accumulation and decumulation retirement stages.

I am somewhat leery of online calculators because it is not always obvious what assumptions are built in to them. Some experts on online calculators recommend to always use more than one to cross check your results. I have read that some of the popular calculators can have widely varying results. I do not want to totally bet the future on a black box.

The online calculators still have one important feature you cannot practically emulate: monte carlo simulations.

Keepcalm
Posts: 96
Joined: Sat May 13, 2017 7:51 pm

Re: Using FV,PV, and PMT for retirement tracking

Post by Keepcalm » Sun Jun 18, 2017 7:02 am

carolinaman wrote:I have used these functions in Excel for retirement planning. Whenever I did, I also setup linear spreadsheets to verify results and also see progression on a year by year basis. I could quickly change variables in the spreadsheet (investment return, inflation, savings) to see the impact. I always want to cross check my calculations, and using combination of math functions and spreadsheets was good. I used spreadsheets for both accumulation and decumulation retirement stages.

I am somewhat leery of online calculators because it is not always obvious what assumptions are built in to them. Some experts on online calculators recommend to always use more than one to cross check your results. I have read that some of the popular calculators can have widely varying results. I do not want to totally bet the future on a black box.

The online calculators still have one important feature you cannot practically emulate: monte carlo simulations.


When you say you setup linear spreadsheets are you referring to comparing your excel calculations to actual results as you progress? Also if that is in fact what you meant, are you saying you adjust your FV numbers to reflect the actual return versus what you had originally?

User avatar
whodidntante
Posts: 1954
Joined: Thu Jan 21, 2016 11:11 pm

Re: Using FV,PV, and PMT for retirement tracking

Post by whodidntante » Sun Jun 18, 2017 7:46 am

A monte carlo analysis would be a bit better, but the model for future returns would be the source of error. That will let you look at the impact of sequence of returns and risk of ruin once you stop accumulating. Historical data would likely be too rosey unless you make an adjustment for the currently high valuations. Though we are also rolling out AI and automation more and more, and EM is shining lately, so maybe stocks will never be this cheap again.

carolinaman
Posts: 2502
Joined: Wed Dec 28, 2011 9:56 am
Location: North Carolina

Re: Using FV,PV, and PMT for retirement tracking

Post by carolinaman » Sun Jun 18, 2017 2:24 pm

Keepcalm wrote:
carolinaman wrote:I have used these functions in Excel for retirement planning. Whenever I did, I also setup linear spreadsheets to verify results and also see progression on a year by year basis. I could quickly change variables in the spreadsheet (investment return, inflation, savings) to see the impact. I always want to cross check my calculations, and using combination of math functions and spreadsheets was good. I used spreadsheets for both accumulation and decumulation retirement stages.

I am somewhat leery of online calculators because it is not always obvious what assumptions are built in to them. Some experts on online calculators recommend to always use more than one to cross check your results. I have read that some of the popular calculators can have widely varying results. I do not want to totally bet the future on a black box.

The online calculators still have one important feature you cannot practically emulate: monte carlo simulations.


When you say you setup linear spreadsheets are you referring to comparing your excel calculations to actual results as you progress? Also if that is in fact what you meant, are you saying you adjust your FV numbers to reflect the actual return versus what you had originally?


No. For example, I would setup a linear accumulation stage of say 20 years to project accumulation of funds during that time. I would then setup decumulation spreadsheet to show that stage. I used 30 years for that. You can adjust your variables to see how your plan performs and how long it lasts. It may sound complicated but it is really simple if you have basic Excel knowledge.

I also updated it as years progressed to reflect current results. I also used what I considered reasonably conservative numbers. I did mine using inflation as a variable. Today, I would use just a real return because it seems simpler.

I hope this helps.

Post Reply