Russian, Ukrainian & FSU Information & Discussion Forums

General Discussion => General Chat => Topic started by: shakespear on May 06, 2019, 04:19:53 PM

Title: Help With An Excel Spreadsheet
Post by: shakespear on May 06, 2019, 04:19:53 PM
Anybody good with excel spreadsheets?

I'm trying to develop a spreadsheet that will tell me the draw-down of a portfolio
value considering the continuing annual rate of return and the size of the monthly
withdrawal.

I believe I've done that with the following formula -

FV = ( (a/12), (H), (F), (0-CV) )

FV = future value

A = annual rate of return, compounded monthly

H = number of draw down months

F = monthly draw down

CV = current value

I'd like to add in a calculation for inflation - figuring how much I'd need to increase my
monthly draw down to account for a certain percentage of inflation annually and the
impact that would have on my future value of investment.

Can anyone suggest to to incorporate that into my original calculation?       
Title: Re: Help With An Excel Spreadsheet
Post by: yankee on May 06, 2019, 05:12:52 PM
Anybody good with excel spreadsheets?

I'm trying to develop a spreadsheet that will tell me the draw-down of a portfolio
value considering the continuing annual rate of return and the size of the monthly
withdrawal.

I believe I've done that with the following formula -

FV = ( (a/12), (H), (F), (0-CV) )

FV = future value

A = annual rate of return, compounded monthly

H = number of draw down months

F = monthly draw down

CV = current value

I'd like to add in a calculation for inflation - figuring how much I'd need to increase my
monthly draw down to account for a certain percentage of inflation annually and the
impact that would have on my future value of investment.

Can anyone suggest to to incorporate that into my original calculation?     

what is the equation?
Title: Re: Help With An Excel Spreadsheet
Post by: shakespear on May 06, 2019, 10:21:48 PM

what is the equation?

Don't know.  Excel does the formula for me.
Title: Re: Help With An Excel Spreadsheet
Post by: shakespear on May 07, 2019, 09:09:28 AM
Ya think this might work?

FV = CV*(1+A/12)^H – F*((1+A/12)^H – (1+Q)^H))/(A/12 – Q)

FV = future value
CV = current value
A = annual rate of return
H = number of draw down months
F = first monthly draw down amount
Q = monthly rate of inflation