Personal Financial Roadmap (working years)
In your working years
You are working and earning money for daily expenses, paying mortgages and saving for future expenses such as your children's college fund, and your retirement, etc.
This is the tool you need to find an optimized roadmap running through all of those targets smoothly and avoiding struggle times lacking money. Using this tool you can keep a relatively stable spending over income ratio and living condition in your whole life.
This tool helps you quickly respond to any change in income, expenses, inflation, etc.
This page gives step-by-step instructions on how to use the spreadsheet PFRp1 in financial planning. When you retire, you need to use the spreadsheet PFRp2 to monitor your retirement fund.
About the calculation (click here to read the details)
The formulas in this tools based on following simple principles:
- The Spending S gradually increases at the rate of inflation to keep a stable living condition.
- The Income I minuses the Spending S and the Installment N is the Investment V.
- The Income I increases and the Installment N changes over time, therefore the Investment V changes too.
- The big Planned Money amounts M needed at every target time T are the result of every small Investment V.
- If the Money M is not enough at the target time T, we have to defer the time T or we have to decrease the Planned Money M.
- If the Income I in the calculated result is higher than it is in real life, we have to decrease the Installment N and the Spending S and the Planned Money M.
- If the Income I in the calculated result is lower than it is in real life, we may increase the Installment N and the Spending S and the Planned Money M, or shorten the time that means retire early.
- Any change in any above numbers at any time affects the whole roadmap.
The formulas in this tools based on following simple principles:
- The Spending S gradually increases at the rate of inflation to keep a stable living condition.
- The Income I minuses the Spending S and the Installment N is the Investment V.
- The Income I increases and the Installment N changes over time, therefore the Investment V changes too.
- The big Planned Money amounts M needed at every target time T are the result of every small Investment V.
- If the Money M is not enough at the target time T, we have to defer the time T or we have to decrease the Planned Money M.
- If the Income I in the calculated result is higher than it is in real life, we have to decrease the Installment N and the Spending S and the Planned Money M.
- If the Income I in the calculated result is lower than it is in real life, we may increase the Installment N and the Spending S and the Planned Money M, or shorten the time that means retire early.
- Any change in any above numbers at any time affects the whole roadmap.
Sheets in PFRp1 (click here to see the details)
- Instruction: some important notices
- Common: basic data for calculation
- Retirement: calculate the retirement fund, the ultimate target in financial roadmap
- Installments: children expenses, long term mortgages, house, car, etc.
- Targets: other financial targets, house, children college funds, etc.
- Insurance: spending to protect your financial plan
- Roadmap: detailed result of calculating
- Expense: template to list your expense
Step-by-step instruction
Make a copy of PFRp1, read the instruction (click here to read)
Click here to open the template spreadsheet, then use the menu File→Make a copy to have a copy of PFRp1 in your Google Drive, Noted that: use the menu under the name PFRp1 in the web browser windows, don't use the menu of the web browser, don't download PFRp1 to your local disk, it can't be used in Excel. On the mobile app Google Sheets, please use the menu ⋮→Share & export to Make a copy. Then, you rename your new file to PFRyyyymm after the starting month of this plan.
Please read the Instruction sheet thoroughly before starting the following steps.
The basic data
Please fill in your name, your year of birth, and the year to start the plan (normally it is the current year or next year) in B3, B4, B5 of the sheet Common.
Write the total balance of your portfolio (estates, stocks, mutual funds, 401(k), IRA, etc.) to B6. Your portfolio are to be topped up monthly until your retirement. The value of the house which you are living in is not counted in until you sell it.
Write the estimated average return on investment in B7.
Write the percentage of year to year increment of savings amount in B8. You can increase the savings amount when your income is raised over time.
Increasing the saving amount over time is the trick that helps you start building your wealth easily. You can start with a moderate amount and increase it over time.
B10 is your family average monthly spending. It does not include spending foryour children, house rent or house installment (if any). It does include irregular spending such as a vacation, a new phone/TV, home fixing, etc.
B11 is the average inflation rate in recent years.
Your retirement fund
The sheet Retirement will calculate the volume of your retirement fund that will be enough to keep your living condition equivalent to the present. That amount is affected by the inflation rate.
Write into B4 cell your planned retirement age. You can retire early when your retirement fund is big enough.
Write your expected lifespan in B6 cell. It should be more than 90 to be sure that your retirement fund will last long enough.
B7 is the estimated average inflation rate when you retire. It can be approximately the same as the present.
Your spending in retirement may be more than at present. For example, you may need to spend more on health care. Please write a ratio between 1 and 2 in B8 to reflect that increment.
B13 is your income in retirement. It includes your pension, house rental, benefit from life insurance policies, etc. It does not include withdrawal of your 401(k) fund.
B14 is the lump sum of money you will get when you start retiring. It may be benefit from some term life insurance policies.
B15 is the amount of money you plan to leave after passing away. If you plan to leave your house, this amount is not necessarily big.
B16 is the volume of your needed retirement fund. It is calculated from the above information.
List of installments
Fill the sheet Installments with your current and planned installments. For example a car, a house, college debt, etc.
What makes this tool different is that we count the monthly children expenses as installments from birthday to college graduation. You fill in as many lines as your (planned) number of children.
If you are living in a rental house and you plan to buy a house in the future, write rental expense in this sheet as an installment. However, if you decide not buying house forever, count your rental expense in monthly expenses in the sheet Common.
Try to elaborate your list as detailed as possible. You can write at most 40 lines in this sheet.
List your financial targets
Your saving targets are listed in the sheet Targets. They are some needed amounts of money at some specific times in the future. They can be money to give birth your children, college funds for your children, down payment for your future house, money to fix your house, etc.
You will write the necessary amount in the column C, and the inflation rate of every expenses in the column D. The future value of this expense will be calculated and shown in the column E. You can write the value ready for every expense, for example an insurance policy or jewellery in the column F. This tool will deduct that value from column E when calculating.
You can write at most 20 targets in this sheet.
Insurance expenses
You will list in the sheet Insurance all of your current and planned insurance policies.
Annually premiums will be listed in the column E. Those values are counted as your expense when calculating.
Rows 1&2 summarize the values of all policies. Some of them may be part of your children's college fund, and will appear in the column F of the sheet Targets. Another value, H2, is the total annual benefit you will receive from the year you retire, this value will appear in B13 of the sheet Retirement.
Notice
Your first roadmap should have moderate targets, and you will increase these targets over time.
All sheets in this file are connected. For example:
- No target ends after the year you retire. This tool will overstrike any target or installment or insurance policy which lasts after the year you retire. In this case, you should shorten your targets, installments, insurance policies.
- Expenses for raising a child follow giving birth and is followed by college fund for that child.
View and adjust your roadmap
Calculating
After filling in all necessary information, please move to the sheet Roadmap and choose the value frm1 in the cell F1, this tool will start calculating and showing the roadmap.
The sheet Roadmap has six columns: Year, Portfolio value (at the end of year), Monthly Expense, Installment per month (and insurance premiums), Invest per month, Monthly income. Each row has numbers for one year in the plan.
The bold line on top of this sheet tells you how much you can spend from your income. This ratio will be unchanged in this whole roadmap. This is the advantage of this tool.
The next sheet is the Chart with two lines: portfolio value in blue and invest per month in red.
The portfolio value increases gradually and reaches your target at your planned retirement year. After that year, it may increase for some years then decreases. In your working years, the portfolio value may go down at some point, the reason is you reach a target in that year and spend the planned amount of money.
If the portfolio value goes down under zero at some point, this roadmap is impractical. In that case, you should lower your expenses, lower your installment and lower or postpone your targets.
The invest per month also increases gradually until your retire year, thank to your increased income, and it is zero after your retire. There are some turning points on this red line. Those are years you start or end an installment. The invest per month may be negative in some years, that means you have to spend money from your portfolio in those years. This situation is normal as long as your portfolio value is positive.
Adjust your roadmap
Please pay attention to the cell F3!
If your actual monthly income is greater than that number, congratulations! You have room for improvement. You can spend more, increase your target values, retire earlier. You may try to make these adjustments and recalculate your roadmap.
If your actual monthly income is less than that number, your roadmap is impractical. You must spend less, lower or postpone your target, lower or postpone your installments. Please make these adjustments and recalculate your roadmap.
To spend less, you must know where your money goes and cut the less necessary expenses. You may use the template in the sheet Expenses to sort your expenses.
Plan-Do-Check-Act
The calculation in this tool depends on variable inputs such as inflation rate, return on investment, income and expenses. You should renew your roadmap whenever any of those inputs changes or at least once per year. You should make a copy of the latest file and work on the new file.
Scenarios
You should make several roadmaps for different scenarios and choose the best one. The different scenarios are: own a house or not, house in big city or suburb area, one child or many children, etc.