Compound interests with indexed withdrawals

[tomaifauchai]

hi,

I have a base amount of 100,000$ that i want to spend over 15 years and based on a 5% interest and also a yearly withdrawal inflation of 1.5%. (Think of a retirement increase)
To me it looks like a mix of FV and PV but i'm stuck because of the yearly inflation. What i really want to know is what would be my first year withdrawal. Then after i just add 1.5% to it every years.

I'm looking for a single or multiple formulas that would return the first withdrawal based on the following items. For simplicity let's say i do only 1 annual withdrawal on January 1st.


Years (fixed): 15
Interest(fixed): 5% annual
Withdrawal increase : 1.5% annually
Base amount: 100,000

First Withdrawal: Unknown


FV takes only account of 100,000, 5% and 15 years and fixed withdrawals/payments.

Thanks

[tomaifauchai]

Attachment 668170

If this can help to solve my problem
PMT solves the money i want to withdraw every year during 15 years. But i want that amount to increase by 1.5% per year.
I did the calculations manually, but you can see i actually earn more money (2814$) because i withdraw less in the beginning, which is normal.

But is there a way to factor that payment raise into a formula so it just give me the "correct" starting payment amount in green?

Thanks

[FlameRetired]

Hello tomaifauchai. Welcome to the forum.

Pictures are nearly worthless at this end.

Please attach a representative Excel workbook file. Instructions are in the 'gold' information bar at the top of each page in this forum.

[tomaifauchai]

Here the workbook. The desired pattern is in Column F.
To give an example, i started in the middle at F12 and reduced 1.5% per years up to F5 and raised 1.5% per years down to F19.

There must be a formula to get that behavior and have I19 worth 0$ like H19 is.

Column H is basically the verification of Column E and Column I should be the verification of Column F.

Thanks again.

[tomaifauchai]

I found it. The formula is called - Growing Ordinary Due Annuity Payment. (beginning of period)


If the discount rate and the growth rate are not equal, the payment formula is:

P = PV * (r - g) /[(1 - ((1 + g) / (1 + r))^n) * (1 + r) * (1 + g)^(pn - 1)]

If the discount rate and the growth rate are equal, the payment formula is not supported.

P = PV / n * (1 + g)^(pn - 1)

Where:

PV = Present Value

P = Payment

r = Discount Rate / 100

g = Payment Growth Rate / 100

pn = Payment Number

n = Number Payments