Coast FIRE Formulas for Excel and Google Sheets

The Coast FIRE number fits in one spreadsheet cell: =Spending/SWR/((1+Return)/(1+Inflation))^(RetireAge-Age). Two more formulas built on Excel’s NPER and PMT functions tell you when your contributions get you there and how much a month you’d need to get there by a certain age.

If you want the reasoning behind the number, the Coast FIRE number guide walks through it. This page is the spreadsheet side: exact syntax, the sign conventions that trip people up, and a check against the month-by-month model our calculator uses.

The Coast FIRE number in one cell

Every example below uses one person. Wes is 36, has $140,000 invested, adds $1,500 a month, and wants to retire at 65 on $52,000 a year in today’s dollars. Set his inputs up like this, with the percentages formatted as percentages so that 7% is stored as 0.07:

     A                      B
1    Current age            36
2    Retirement age         65
3    Yearly spending        52000
4    Expected return        7%
5    Inflation              3%
6    Withdrawal rate        4%
7    Invested today         140000
8    Monthly contribution   1500

The Coast FIRE number goes in B10:

B10  =B3/B6/((1+B4)/(1+B5))^(B2-B1)

For Wes that returns $430,619. Written with the real return spelled out, the formula is longer but gives the same answer:

=B3/B6/(1+((1+B4)/(1+B5)-1))^(B2-B1)

The inner part, (1+B4)/(1+B5)-1, is the real return: 3.88% a year for Wes. I’d give it its own cell (B9) anyway, because the NPER and PMT formulas below need it.

The same number with PV and FV

Excel and Google Sheets both have a present-value function, and the Coast FIRE number is exactly a present value: the amount that grows into your FIRE number with no further deposits.

=PV(B9, B2-B1, 0, -B3/B6)

PV’s arguments are rate, number of periods, payment and future value. The future value goes in as a negative number because Microsoft’s PV documentation treats money you pay out as negative and money you receive as positive. Leave the minus sign off and you get the right figure with the wrong sign.

You can also do the whole thing in nominal dollars. Inflate Wes’s spending to what it will cost at 65, turn that into a FIRE number, and discount it at the full 7%:

=PV(B4, B2-B1, 0, -FV(B5, B2-B1, 0, -B3)/B6)

The FV part says $52,000 of spending today costs about $122,541 a year in 29 years at 3% inflation. Discounted at 7%, that lands on $430,619, to the dollar, the same as the real-return version. That’s a good way to convince yourself the real-return formula is exact rather than a shortcut.

Why not just subtract inflation?

Because 7% minus 3% is 4%, and the true real return is 3.88%. The exact formula works out to (return − inflation) ÷ (1 + inflation). Subtracting skips the division, so it always overstates growth a little, and the error compounds for every year in the exponent.

Here’s how much it moves Wes’s number, 29 years out with a $1,300,000 FIRE target:

The shortcut always makes the target look smaller than it is. At 7% and 3% it’s 3.2% too low. With the 10% return some people use for an all-stock portfolio, the gap grows to 5.4%, because the error grows with the real return. Notice too that 8% with 4% inflation isn’t the same as 7% with 3%, even though both subtract to 4%. The exact formula costs one extra set of parentheses, so there’s no reason to use the shortcut in a spreadsheet.

How many months until I can coast?

NPER answers this, once you see the trick. Your Coast FIRE line rises every month at the real return, and so does your balance. The gap between them behaves like a loan that grows at the real return while your contributions pay it down. The month the loan hits zero is the month you can coast.

That turns into two cells:

B11  =(1+B9)^(1/12)-1                  monthly real rate
B12  =NPER(B11, -B8, B10-B7)           months until you can coast
B13  =B1+ROUNDUP(B12,0)/12             age you can coast

NPER takes rate, payment and present value, in that order, and wants the rate per period, so the monthly rate is (1 + real return) to the power 1/12, minus one. The payment is negative and the gap is positive, the same money-out, money-in convention as PV. Google Sheets uses the same argument order under different names (NPER in Sheets).

For Wes, the gap is $290,619 and NPER returns 301.6 months. Our calculator checks his balance against the line at the start of each month, so it can only report a whole month: month 302, age 61. That’s why B13 rounds up. The formula finds the exact point where the lines cross, and the calculator finds the first month he’s past it, so the two can differ by up to a month. Round down and you’d stop contributing a month before your balance actually reaches the line.

The table runs both for a range of monthly amounts:

At $1,000 a month, NPER gives 812.4 months, far more than the 348 months Wes has until 65, so he never coasts. NPER doesn’t know about retirement; it’ll happily return a date after you’ve stopped working. Compare the result with the months you have left:

=IF(B12>(B2-B1)*12, "Not before retirement", B1+ROUNDUP(B12,0)/12)

When the contribution is smaller than one month’s growth on the gap, the gap never closes and NPER returns an error instead of a number. Wrapping the formula in IFERROR with the same “Not before retirement” message covers that case too. If you’re already over the line, B10-B7 is negative and NPER comes back negative, so MAX(0, B12) is worth adding.

How much a month to coast by a given age?

PMT runs the same loan backwards: give it the number of months and it tells you the payment.

=-PMT(B11, (45-B1)*12, B10-B7)

The minus sign in front turns PMT’s negative answer into a positive monthly amount. Replace 45 with whatever age you’re aiming for.

Put any rounded-up figure into the calculator and it reports that exact age. Round down instead and the calculator shows you reaching the line 1 month later, because less than a dollar a month short still leaves you short at the deadline. Round up to a figure you’ll actually set as an automatic transfer.

Mistakes that break these formulas

The most common one is using the yearly return divided by 12 as the monthly rate. That’s how loan formulas usually work, and it’s wrong here. Dividing 3.88% by 12 compounds to 3.95% a year, so the gap in the NPER formula grows faster than the rate you used to work out the Coast FIRE number. More growth on the gap means more months to close it. With B9/12 in place of B11, NPER gives Wes 305.2 months instead of 301.6, about 4 months later than our calculator.

The second is typing 7 instead of 7% in the return cell. The formulas then assume a 700% return, and your Coast FIRE number comes out close to zero.

The third is mixing a nominal return with spending in today’s dollars. Leave inflation out of the formula and a 7% return makes Wes’s number $182,732 instead of $430,619. Our page on the Coast FIRE calculator with inflation goes into what that does to a plan.

The last one isn’t really a mistake. If your sheet adds contributions once a year, as the free Coast FIRE spreadsheet does to keep its formulas short, the age can land up to a year later than a monthly model. The Coast FIRE number itself doesn’t change, because it doesn’t involve contributions at all. The methodology page lays out exactly how our calculator steps through each month, if you want to rebuild it cell for cell.