Instructions

  1. Copy the sections below into a new spreadsheet
  2. Only modify cells marked as “INPUT” (highlighted in yellow in actual spreadsheet)
  3. All other cells contain formulas that auto-calculate
  4. Use the scenario comparison tabs to evaluate different options

SHEET 1: MAIN CALCULATOR

SECTION A: INPUT VARIABLES (Rows 1-20)

RowColumn A (Label)Column B (Value)Column C (Formula/Notes)
1PERSONAL INFORMATION
2Current Age41INPUT
3Desired Retirement Age50INPUT
4Life Expectancy100INPUT
5
6FINANCIAL INPUTS
7Current Retirement Savings$1,000,000INPUT
8Annual Savings (Pre-Retirement)$0INPUT
9Pre-Retirement Return Rate5.0%INPUT
10Post-Retirement Return Rate6.0%INPUT
11Inflation Rate2.5%INPUT
12
13EXPENSES
14Current Annual Expenses$112,090INPUT
15Retirement Annual Expenses$89,672INPUT
16Tax Rate in Retirement25%INPUT
17Healthcare (included in expenses?)YESINPUT (YES/NO)
18Annual Healthcare Cost (if separate)$20,000INPUT (only if row 17 = NO)
19
20LEGACY GOAL$500,000INPUT

SECTION B: SOCIAL SECURITY INPUTS (Rows 22-35)

RowColumn AColumn BColumn C
22SOCIAL SECURITY - PERSON 1 (Higher Earner)
23SS Full Retirement Age (FRA)67INPUT
24SS Benefit at Age 62$33,762INPUT
25SS Benefit at Age 65$41,808INPUT
26SS Benefit at Age 67 (FRA)$48,232INPUT
27SS Benefit at Age 70$59,808INPUT
28Desired SS Claiming Age65INPUT
29
30SOCIAL SECURITY - PERSON 2 (Lower Earner)
31SS Benefit at Age 62$19,384INPUT
32SS Benefit at Age 65$24,000INPUT
33SS Benefit at Age 67 (FRA)$27,692INPUT
34SS Benefit at Age 70$34,338INPUT
35Desired SS Claiming Age65INPUT

SECTION C: OTHER INCOME (Rows 37-40)

RowColumn AColumn BColumn C
37OTHER GUARANTEED INCOME
38Rental Income (Annual)$18,000INPUT
39Pension (Annual)$0INPUT
40Other Income (Annual)$0INPUT

SECTION D: CALCULATED RESULTS (Rows 42-65)

RowColumn AColumn BColumn C (Formula)
42TIMELINE
43Years Until Retirement=B3-B2Formula: Retirement Age - Current Age
44Years in Retirement=B4-B3Formula: Life Expectancy - Retirement Age
45Years Until SS (Person 1)=B28-B3Formula: SS Claim Age - Retirement Age
46Years Until SS (Person 2)=B35-B3Formula: SS Claim Age - Retirement Age
47
48PORTFOLIO AT RETIREMENT
49Current Savings=7Reference to input
50Growth from Current Savings=B7*(((1+B9)^B43)-1)Formula: FV of current savings
51Future Value of Annual Savings=IF(B8=0,0,B8*(((1+B9)^B43-1)/B9))Formula: FV of annuity
52Total Portfolio at Retirement=B49+B50+B51KEY OUTPUT
53
54RETIREMENT INCOME ANALYSIS
55Gross Income Needed (Annual)=B15/(1-B16)Formula: Expenses / (1-Tax Rate)
56Person 1 SS (when claimed)=CHOOSE(B28-61,B24,0,0,B25,0,B26,0,0,B27)Lookup based on claim age
57Person 2 SS (when claimed)=CHOOSE(B35-61,B31,0,0,B32,0,B33,0,0,B34)Lookup based on claim age
58Total Social Security=B56+B57
59Rental Income=38
60Other Income=39+40
61Total Guaranteed Income=B58+B59+B60
62Required Portfolio Income=B55-B61KEY OUTPUT
63
64SUSTAINABILITY CHECK
65Annual Portfolio Income Need=B62

SECTION E: PRESENT VALUE CALCULATION (Rows 67-85)

Row 67: STARTING POOL CALCULATION
Row 68: (Uses PV formula to calculate needed portfolio at retirement)


Cell B68: Years before SS starts (Person 1)
=MAX(B28-B3,0)


Cell B69: Years before SS starts (Person 2)
=MAX(B35-B3,0)


Cell B70: Years before ANY SS
=MAX(MIN(B28,B35)-B3,0)


Cell B71: Portfolio need during SS-wait period
=IF(B70=0,0,B55-B38-B39-B40)


Cell B72: PV of pre-SS withdrawals
=IF(B70=0,0,B71*((1-(1+B10)^(-B70))/B10))


Cell B73: Portfolio need after SS starts
=B62


Cell B74: Years after SS starts
=B44-B70


Cell B75: PV at retirement of post-SS withdrawals
=IF(B74<=0,0,B73*((1-(1+B10)^(-B74))/B10)/((1+B10)^B70))


Cell B76: PV of legacy goal
=B20/((1+B10)^B44)


Cell B77: TOTAL STARTING POOL NEEDED
=B72+B75+B76


Cell B79: Portfolio You'll Have
=B52


Cell B80: Surplus/(Shortfall)
=B79-B77


Cell B81: Surplus as % of Need
=B80/B77


Cell B83: CAN YOU RETIRE?
=IF(B80>0,"YES - Surplus of " & TEXT(B80,"$#,##0"),"NO - Shortfall of " & TEXT(ABS(B80),"$#,##0"))

SHEET 2: YEAR-BY-YEAR PROJECTION

Headers (Row 1)

ABCDEFGH
AgeYearStart BalanceSS IncomeOther IncomeTotal IncomeExpensesGrowth

Continuing in Row 1:

IJKL
After ExpensesNet Cash FlowWithdrawalEnd Balance

Formula Row (Row 2 - for age = retirement age)

Cell A2: =Sheet1!$B$3
Cell B2: =A2-Sheet1!$B$2+2025
Cell C2: =Sheet1!$B$52


Cell D2 (SS Income):
=IF(A2>=Sheet1!$B$28,Sheet1!$B$56,0)+IF(A2>=Sheet1!$B$35,Sheet1!$B$57,0)


Cell E2 (Other Income):
=Sheet1!$B$38+Sheet1!$B$39+Sheet1!$B$40


Cell F2 (Total Income):
=D2+E2


Cell G2 (Expenses):
=Sheet1!$B$55


Cell H2 (Growth on start balance):
=C2*Sheet1!$B$10


Cell I2 (After Expenses):
=C2+F2-G2


Cell J2 (Net Cash Flow):
=F2-G2


Cell K2 (Withdrawal):
=MAX(0,G2-F2)


Cell L2 (End Balance):
=C2+H2-K2

Formula Row 3+ (copy down to age 100)

Cell A3: =A2+1
Cell B3: =B2+1
Cell C3: =L2  (previous end balance)
Cell D3: =IF(A3>=Sheet1!$B$28,Sheet1!$B$56,0)+IF(A3>=Sheet1!$B$35,Sheet1!$B$57,0)
Cell E3: =Sheet1!$B$38+Sheet1!$B$39+Sheet1!$B$40
Cell F3: =D3+E3
Cell G3: =Sheet1!$B$55
Cell H3: =C3*Sheet1!$B$10
Cell I3: =C3+F3-G3
Cell J3: =F3-G3
Cell K3: =MAX(0,G3-F3)
Cell L3: =C3+H3-K3

Copy formulas from Row 3 down to Row where Age = 100

SHEET 3: SCENARIO COMPARISON

Setup

Compare different retirement ages and SS claiming strategies side-by-side.

RowAB (Scenario 1)C (Scenario 2)D (Scenario 3)E (Scenario 4)
1SCENARIOBase PlanEarly RetireWait for SSConservative
2
3INPUTS
4Retirement Age50485052
5SS Claiming Age (P1)65626765
6SS Claiming Age (P2)65626765
7
8OUTPUTS
9Portfolio at Retirement=$1,551,328=$1,407,100=$1,551,328=$1,710,339
10Starting Pool Needed
11Surplus/(Shortfall)
12
13INCOME IN RETIREMENT
14Combined SS Annual
15Other Income
16Portfolio Withdrawal
17Total Annual Income
18
19KEY METRICS
20Years Until SS
21Portfolio at Age 65
22Portfolio at Age 80
23Portfolio at Age 100
24Lifetime SS Benefits
25Can Retire?

SHEET 4: SOCIAL SECURITY CALCULATOR

Calculate SS benefits at different ages

RowABC
1PERSON 1 (Higher Earner)
2
3Full Retirement Age67INPUT
4Benefit at FRA$48,232INPUT
5
6Calculated Benefits:
7At Age 62 (70% of FRA)=B4*0.70Auto-calc
8At Age 63 (75% of FRA)=B4*0.75Auto-calc
9At Age 64 (80% of FRA)=B4*0.80Auto-calc
10At Age 65 (86.7% of FRA)=B4*0.867Auto-calc
11At Age 66 (93.3% of FRA)=B4*0.933Auto-calc
12At Age 67 (100% - FRA)=B4Auto-calc
13At Age 68 (108% of FRA)=B4*1.08Auto-calc
14At Age 69 (116% of FRA)=B4*1.16Auto-calc
15At Age 70 (124% of FRA)=B4*1.24Auto-calc
16
17PERSON 2 (Lower Earner)
18Benefit at FRA$27,692INPUT
19
20At Age 62=B18*0.70
21At Age 63=B18*0.75
22At Age 64=B18*0.80
23At Age 65=B18*0.867
24At Age 66=B18*0.933
25At Age 67 (FRA)=B18
26At Age 68=B18*1.08
27At Age 69=B18*1.16
28At Age 70=B18*1.24

SHEET 5: SENSITIVITY ANALYSIS

Test how changes in assumptions affect your plan:

RowABCDEF
1VariableBase Case-2% Scenario-1% Scenario+1% Scenario+2% Scenario
2
3Return Rate6%4%5%7%8%
4Portfolio at Retirement
5Starting Pool Needed
6Surplus/(Shortfall)
7
8Expenses$89,672$105,672$97,672$81,672$73,672
9Portfolio at Retirement
10Starting Pool Needed
11Surplus/(Shortfall)
12
13Life Expectancy1009095105110
14Starting Pool Needed
15Portfolio at End

CONDITIONAL FORMATTING SUGGESTIONS

For Main Calculator (Sheet 1):

  1. Input cells (Column B, rows with “INPUT”): Fill with light yellow
  2. Key outputs (B52, B62, B77, B80): Fill with light green
  3. Cell B83 (“Can You Retire?”):
    • Green if contains “YES”
    • Red if contains “NO”
  4. Surplus/Shortfall (B80):
    • Green if > 0
    • Red if < 0

For Year-by-Year (Sheet 2):

  1. End Balance (Column L):
    • Green if > $500,000
    • Yellow if between 500,000
    • Red if < $100,000

CHARTS TO CREATE

Chart 1: Portfolio Projection Over Time

  • X-axis: Age (from Sheet 2, Column A)
  • Y-axis: End Balance (from Sheet 2, Column L)
  • Type: Line chart
  • Add horizontal line at $500,000 (legacy goal)

Chart 2: Income Sources Stacked Area

  • X-axis: Age
  • Y-axis: Income amount
  • Series 1: SS Income (Column D)
  • Series 2: Other Income (Column E)
  • Series 3: Portfolio Withdrawal (Column K)
  • Type: Stacked area chart

Chart 3: Scenario Comparison

  • X-axis: Scenarios (from Sheet 3, Row 1)
  • Y-axis: Surplus amount
  • Type: Column chart

USAGE INSTRUCTIONS

Quick Start:

  1. Fill in all yellow INPUT cells in Sheet 1
  2. Check Sheet 2 for year-by-year projection
  3. Review Sheet 3 to compare scenarios
  4. Use Sheet 5 to test “what-if” scenarios

To Test Different Scenarios:

  1. Change retirement age (B3)
  2. Change SS claiming ages (B28, B35)
  3. Change return assumptions (B9, B10)
  4. See results update automatically

Key Cells to Watch:

  • B52: Your portfolio at retirement
  • B77: How much you need
  • B80: Your surplus/shortfall
  • B83: Can you retire? (YES/NO)

VALIDATION CHECKS

Add these formulas to verify your inputs make sense:

Cell B86: Retirement age < Current age?
=IF(B3<=B2,"ERROR: Retirement age must be after current age","OK")


Cell B87: SS claiming age valid?
=IF(OR(B28<62,B28>70,B35<62,B35>70),"ERROR: SS age must be 62-70","OK")


Cell B88: Expenses reasonable?
=IF(B15>B14*1.5,"WARNING: Retirement expenses seem high","OK")


Cell B89: Return rates reasonable?
=IF(OR(B9<0,B9>15,B10<0,B10>15),"ERROR: Return rates outside normal range","OK")

NOTES

  • All monetary values should be in today’s dollars
  • Return rates are assumed to be real (after inflation) unless you adjust
  • Social Security benefits shown are in today’s dollars (will adjust for COLA)
  • Healthcare costs are critical - make sure they’re included
  • Consider adding 10-20% buffer for unexpected expenses
  • Review and update annually

Last Updated: November 2025 Based on your specific situation: Age 41, targeting retirement at 50