Retirement planning in nominal dollars 1

Financial Modeling in Google Sheets

Erin Buchanan

Professor

Nominal dollars

  • Real dollar planning assumes uniform stock returns

  • Nominal dollars are the amount of money you have now

  • Nominal dollar planning incorporates a security horizon

  • Model will sell enough stock to maintain expenses through the security horizon

Financial Modeling in Google Sheets

Fixed information

Fixed Information

Financial Modeling in Google Sheets

Yearly information

= Initial balance cell

Initial yearly balance

= Fixed Income Year Beginning + 
  Stock Holding Year Beginning + 
  Annual Withdrawal

Yearly information part 2

Financial Modeling in Google Sheets

Fixed income pv() function

= pv(rate, number_of_periods, -payment_amount)

  • rate: $\frac{1 + Return on Income \times (1 - Ordinary Tax Rate)} { (1 + Inflation Rate)} - 1$
  • number_of_periods: Security Horizon Years - 1
  • payment_amount: Annual withdrawal (negative)
Financial Modeling in Google Sheets

Fixed income in action

=PV((1+$B$3*(1-$B$5))/(1+$B$7)-1,$B$10-1,-F14)

Fixed income pv formula

Financial Modeling in Google Sheets

Stock sales after taxes

= End of Year Fixed Income - Begining of Year Fixed Income - Withdrawal Amount

Stock holding

Financial Modeling in Google Sheets

Stock holdings

= Total Beginning Balance - Fixed Income Beginning Balance - Withdrawal

Stock holding

Financial Modeling in Google Sheets

Stock holdings year 2 and on

= Year End Stock Holding + Stock Sales / (1 - Capital Gains Tax + Capital Gains Tax * Last Years Tax Basis / Last Years End Stock Holding)

=I14+D15/(1-$B$6+$B$6*L14/I14)

Stock selling part 2

Financial Modeling in Google Sheets

Try it yourself!

Financial Modeling in Google Sheets

Preparing Video For Download...