Formula to calculate final value when invested in SWP (in excel)

[Vyom]

I encountered an online SWP calculator that shows the future value of the lump-sum amount that we withdraw at a specific rate. But I didn't know it would be this difficult to make one calculator in excel, and require help for the same.

I found online that the formula is below:

A = PMT ((1+r/n)nt – 1) / (r/n))

Breaking them down:
A: This is the future value of an investment. In simple terms, this is the value of a current asset based on an estimated growth rate.
PMT: PMT is a payment per period in terms of finance.
r: This signifies an annual rate of return.
n: Compound numbers in a given period are denoted by "n."
t: This is the number of periods in which an amount is invested.

But I can't seem to use this formula in excel.
If I use below amount in online calculator, and the same amount in excel formula, I get different results:
Total investment = Rs 1000000
Withdrawal per month = Rs 10000
Tenure (years) = 10
Expected return = 8%
Final Amount = Rs 3,57,682

Now if in excel I do this:
PMT = 1000000
r = 8
n = 1 (since 1 means yearly, 4 means quarterly, and 12 means monthly)
t = 10
(not sure where to enter withdrawal per month amount).
Then I am getting A = 1,01,25,000 (crore :O)

Any help is appreciated. Online calculator: SWP Calculator: Systematic Withdrawal Plan Calculator

 

[Desmond]

Perhaps some BODMAS calculation difference? Usually different calculators have different BODMAS rules which could result in different outputs.

Edit: Reference: Excel’s order of calculation is not BODMAS

 

[Vyom]

Well, how I interpret the below formula:
Formula, A = PMT ((1+r/n)nt – 1) / (r/n))
My interpretation: = PMT * ((1+ (r/n) )* n * t – 1) / (r/n))
But it seems to be incorrect.

 

[nac]

Well, how I interpret the below formula:
Formula, A = PMT ((1+r/n)nt – 1) / (r/n))
My interpretation: = PMT * ((1+ (r/n) )* n * t – 1) / (r/n))
But it seems to be incorrect.

You can't plug in withdrawal amount in this formula.
Use PMT function in Excel and calculate how much you can withdraw every year with your expected returns.
10 Lakhs, 10yrs and 8% gives about 1.49 Lakh withdrawal per year for the next 10 years - by that time you've withdrawn all your money.

BTW it's PMT * ((1+ (r/n) )^ n * t – 1) / (r/n))

 

[Vyom]

BTW it's PMT * ((1+ (r/n) )^ n * t – 1) / (r/n))

Well using the power function or ^, I get values in exponential.

4.84276E+13

 

[nac]

Well using the power function or ^, I get values in exponential.

4.84276E+13

The formula you have is for finding future value if you deposit X amount every month/quarter.
I don't know which numbers you're plugging in to get that result
PMT = Monthly investment/deposit
r = Rate of Interest
n = no. of times compounding per year
t = No. of years

If I plug in 10000, 8%, 10yrs and monthly compounding, I get 18,29,460/-