How do I calculate the lump sum I need in future dollars, for my retirement in Excel?

Hi Shuan, although one may be able to use a calculator to calculate the figure needed as Hariz has done for you (thanks Hariz!), I do wish to highlight some of the risks that you need to account for in retirement. The lumpsum figure you obtain, which you mention that you wish to put into a vehicle that gives 4% p.a. on average (CPF SA notwithstanding), is presumably an investment vehicle that will be exposed to market risk.

With market risks, one of the biggest problems that you will face is something called a sequence of returns. To put it simply, the year you retire, if there is a market crash and your portfolio suffers a loss, you still need to withdraw from the portfolio to meet expenses. You will be withdrawing at a loss, which can greatly impact your portfolio and how long it can last. The reverse is true when markets have a bull run the year you retire, your money will be able to last longer. So although the average return of 4% may be achieved in the long run, you will need to take into account whether you had good returns followed by poor returns when you started your retirement or poor returns followed by good returns.

This article will provide more details: https://www.thebalance.com/how-sequence-risk-af...
While it is good to get a ballpark figure for retirement, your retirement is still a very long period, and longevity is a multiplier of all risks you will face.

Still, if you are 30, there is plenty of time to start working on it. Retirement may be daunting (even I am constantly concerned about my own retirement), but with a solid strategy and action taken, you will be able to better cope with its complexities. Good luck!

Hey Shaun, I just use a retirement calculator app.

So for your scenario, you'll need a $1,367,687.13 as a lumpsum at 65 to give you a Current Value of 3k/mth @ 3% inflation rate for 20 years from 65 to 85 with a post-retirement return of 4% per annum.

If you have 0 saved right now, you need to invest 665.77/mth @ a 8% p.a return to hit that goal.