Personal Finance Companion Documentation

Follow these steps when moving to a new version (only available on version 2021.1.1 and further).

1. Download the new version you want to upgrade to.
2. Open both the new version and the old version you are upgrading from.
3. In the old version, unhide the Export sheet.
4. In the old version’s Export sheet, copy columns C->EG.
5. In the new version’s Import sheet, paste the copied data as values into the same columns (C->EG).

That is all that needs to be done! However, there are a few things to take note of:

• This needs to be done before you do anything else in the new file.
• This will not work if you paste the data as anything other than values.

The “By-person Inputs” are straight-forward. Only the names flow into various other tools in the file.

The “Budgeting” categories are used in the Forecast Data sheet.

No user-input is needed on this sheet since all charts / graphs populate based on the information you enter on the other sheets. This sheet just allows you to look at everything together.

Budgeting on a paycheck-by-paycheck basis with dollars you already have is very important, but so is forecasting the future. This will help shed light on areas that you might need to start saving for now. This tool allows for yearly forecasting for up to two people getting paid twice a month. Given variation in the paycheck frequency between the two individuals, the dates listed (Jan 1, Jan 15, Feb 1…) may not exactly line up. This is okay – it is only a forecasting tool and not a true budget.

Sheets

This tool is unique in that it is broken up into the multiple sheets outlined below.

Forecast Data: This is where all of your forecast level input data goes. Remember to always refresh pivot tables after updating the information in the Forecast Data sheet.

Forecast (Monthly) | (Semi-Monthly): These pivots are built off of the Forecast Data sheet and will dynamically contract/expand based on the data entered. It presents the same information in a format that is easier to analyze and drill down on (via slicers).

Forecast (Chart): These pivot charts dynamically present a graphical view of your forecast while allowing you to drill down on the areas that matter most (via slicers).

Step-by-Step Guide

1. Start by going to the Inputs sheet and entering the first name and initials level data under the “By-person Inputs” section.
2. While in the Inputs sheet, change the yellow cell data under the “Budgeting” section for the main and sub categories you would like. Feel free to add or remove data from this list. Because these lists are built in a table, the Forecast Data sheet will dynamically adjust to what you enter.
3. In the Forecast Data sheet, above each date (Jan 1, Jan 15, Feb 1…) is either “ACT” or “FCST” (for Actual or Forecast). If the date has already passed, change each field for prior dates to “ACT”.
4. Go line-by-line entering name, main category, sub category, and numbers under each relevant month that you have a good idea of what your actual income/expenses were.
5. When you are finished, refresh the pivot tables so that the data flows into the summary sheets.
6. As time goes on and you pass the next date, change it from “FCST” to “ACT” and enter your actual income/expenses as close as you can. The reason for this is that it will help the automatic calculations better forecast future months based on what has come before.

Special note: Do not be afraid to go rogue. Automatic calculations can only get you so far and I cannot automate all possible scenarios. The past does not guarantee what the future will look like so change data in the future columns as needed. This is the one time it is okay to edit grey cells.

User Inputs

Name: Select which individual the data in the row belongs to. Change the available options via the Inputs sheet.

Main Category: Secondary level of detail to break out the information. Change the available options via the Inputs sheet.

Sub Category: Lowest level of detail to break out the information. Change the available options via the Inputs sheet.

ACT | FCST: Choose whether the date in each column has actualized (past date) or if it is still a forecast (future date). Update as time goes on.

Automatic Calculations

Mapping: Automatically maps the row into either Income, Expense, Saving, or Investing based on the main category. Useful for higher level analysis.

Forecasted Dates: FCST Columns have some automatic calculations to do some of the leg work for you (mostly averaging past data). Adjust as needed as automatic calculations cannot pick up all possible scenarios. This is the one time it is okay to edit grey cells.

Track how much of your paycheck is being saved/invested over time. This is useful to see how consistent you are over time.

User Inputs

These are self-explanatory. Feel free to rename rows as needed (for instance if you take part in a 403(b) and not a 401(k). All values are in dollars.

Automatic Calculations

The saving rate is calculated in two ways for you to incorporate taking advantage of employer match (or not).

Calculate how increasing your saving/investing rate over time will help your money grow faster.

User Inputs

Salary: Your annual base salary (pre-tax).

Initial Saving Percentage: The percentage of your annual salary you will start saving.

Annual Percentage Increase: How much you want to increase the saving percentage annually. Set your own cadence for when the increase(s) occur(s). For example, if you want to increase by 4% every year then maybe increase your savings by 1% every three months.

Max Saving Percentage: The max percentage you would save / invest.

Expected Rate of Return: The return on investment from a high-yield savings account or by investing the money. This calculator assumes no fluctuations in this percentage (when in reality fluctuations will occur).

Automatic Calculations

The above inputs are used to put together a 30 year schedule of how saving and/or investing will make your money grow over time.

Assumptions

• The expected rate of return is flat and never fluctuates when in reality the stock market will have ups-and-downs.

Track your assets (cash, investments, etc.) against your liabilities (loans, etc.) to see where your true net worth currently stands and watch it change over time.

User Inputs

Input the dollar amounts year-by-year. Update the current year’s values until 12/31 and then start updating the next year’s in order to see the change in net worth over time.

Automatic Calculations

The inputs are summed and displayed in different ways.

This tool uses your annual salary, 401(k) contribution percentage, and employer match to show where you are now and provide different scenarios for what increasing your contribution percentage would like.

User Inputs

Base Pay: Your annual base salary (pre-tax).

Contribution %: How much of your paycheck is being allocated to your 401(k).

Contribution % Increase: The scenario changes you want to see in your contribution %. For example, if your current contribution percentage is 6% and you put 2% in this cell, it will show you what it would look like to contribute 6%, 8%, 10%, 12%, and 14%.

Employer Match: The percentage your employer will match based on your current contribution %.

Legal Contribution Limit: The current annual limit you can legally contribute into the 401(k). Must be updated on a yearly basis to remain accurate.

Automatic Calculations

Using the above inputs, it will calculate your bi-weekly, monthly, and annual contribution (in dollars) based on the scenario contribution percentages. This is also done for the employer match and totaled to show both as a whole.

Callouts

• The annual legal contribution limit will have to be updated on a yearly basis.
• Since you should always take advantage of the full employer match, this calculator assumes you are already contributing the minimum required to obtain the value you enter in employer match and will not adjust this value in the scenarios.

If you want to hold a percentage of your portfolio in certain funds, stocks, bonds, etc. this tool will help you re-balance based on these target percentages, the current balance, and investable cash.

User Inputs

To Invest Now: How much money you can currently contribute towards re-balancing the portfolio.

Name: The name of the different assets invested in within your portfolio.

Target: The target percentage allocation that you want each investment to maintain within your total portfolio.

Current Balance: The current dollar amount of each investment.

Automatic Calculations

Rebalanced: Taking into account all current balances and target percentages, this lets you know what a re-balanced portfolio would look like.

Adjustment Needed: This shows the amount needed to bridge the gap between your current balanced and a re-balanced portfolio.

Investable Now: Using the amount that you can invest now, this breakds down where to start investing it in order to begin re-balancing the portfolio.

Callouts

• This tool will never tell you to sell an investment in order to re-balance.

Based on your current plan, see how long it will take you to retire within your needs. Then play around with new scenarios to see what adjustments you can make to speed it up.

User Inputs

Retirement Age: The age at which you want to retire.

Working | Retired ROI: The ROI (return on investment) you expect to achieve during your working and retired years respectively.

Retired SWR: The annual “safe withdrawal rate” or percentage of investments you will withdraw each year.

Annual Salary Increase: Estimate by what percentage your salary will increase each year.

SS Benefits Age: The age at which you will take out social security benefits. Taking out earlier means you will not get 100% of the benefits.

Automatic Calculations

In progress.

This tool will dynamically build you a month-by-month payment reduction plan based on the payoff method you select (custom order, snowball – lowest balance first, avalanche – highest interest rate first). It even includes a selection to show how long it would take to pay off the creditors if only paying the minimum monthly payment (and how much interest would accrue).

User Inputs

Method: The order in which you want to pay off the debt. With the snowball method, you pay off the lowest balances first. The avalanche method has you paying off the highest interest rates first. Learn more here.

Balance Date: The date you are entering in all of the information. This doesn’t really need to change once entered the first time.

Monthly Payment: How much money you put towards debt each month. This needs to be higher than or equal to the sum of all minimum payments.

Custom Order: If you choose the “Custom Order” method, the ranking you put here will determine how the debt is paid off.

Creditor: The name of the company or person who you owe.

Balance: The total balance owed.

Rate: The APY (annual percentage yield or interest rate).

Minimum Payment: The lowest amount you absolutely have to pay each month.

Additional: Use this field if you have extra money one month you want to put towards debt to have it factored in.

Automatic Calculations

Snowball: The difference between your monthly payment and the sum of all minimum payments. In other words, how much extra you are putting towards paying off debt above-and-beyond the minimum payments. As line items are paid off, this calculator assumes you reallocate the money you were using on paid off items towards those still remaining.

Interest-Only Payment: How much monthly interest is owed/accruing based on the balance and the rate.

This tool will show everything from the first payment to the last (up to 30 year loan term), whether it’s a mortgage or not. Play around with different scenarios such as rates, term lengths, extra monthly payments, etc. and watch it dynamically rebuild the amortization schedule around your inputs.

User Inputs

Loan Type: Whether or not the loan is for a mortgage or not.

Loan Start Date: The date the loan will start. This is used to create the payment schedule.

Loan Amount: How much the loan is for.

Annual Interest Rate: The APR (Annual Percentage Rate) on a loan to secure the purchase. This assumes a fixed rate that never changes over the course of the loan.

Loan Period (in years): The number of years the loan will be active. (If you have it in months just divide it by 12 to arrive at the number of years.)

Payments per year: How many payments you plan on making in a year. Usually this value will be 12 (one payment per month) but you can adjust it as necessary (up to 360 payments over the life of the loan).

Extra Payment: If you plan on paying more than the calculated monthly payment (to pay off faster) enter the value here and the amount will be added to every payment in the schedule.

Property Tax: How much property tax you would have to pay (entered on a per-month basis. If you have an annual number just divide it by 12 and enter the value here). This is only used for a mortgage calculation to arrive at a truer monthly payment.

Automatic Calculations

Scheduled Payment: The estimated monthly payment based on the loan inputs.

Scheduled # of Payments: Uses the loan period and payments per year to determine the max possible number of payments.

Actual # of Payments: Uses the payment schedule to determine when the loan is paid off and reports the total number of payments to reach that point here. It will cap at 360 even if the loan is not paid off at that point (see callout below).

Total Early Payments: If you pay X amount extra per month, the amount is summed and captured here to show how it helps reduce the amount of interest you end up paying.

Total Interest: How much interest accrues and is paid over the course of the loan.

Total Cost of Loan: The original amount plus interest paid over the course of the loan.

Assumptions

• The APR (Annual Percentage Rate) is fixed and does not change over the course of the loan period.

Callouts

• The calculator cannot currently handle more than 360 payments (one payment per month for 30 years). This should be fine for most situations.

This calculator is geared towards quickly determining the monthly payment and how much interest will be paid over the course of a loan. There are elements geared towards vehicle purchases but by excluding trade-in value it can work for other loans as well.

User Inputs

Purchase Price: The total cost of the vehicle (or loan) before the down payment.

Down Payment: How much available cash can be paid from day one. Leave blank if not using for a vehicle loan.

Trade-in Value: How much cash can be applied against the loan from trading in (or selling) a vehicle. Leave blank if not using for a vehicle loan.

Annual Interest Rate: The APR (Annual Percentage Rate) on a loan to secure the purchase. This assumes a fixed rate that never changes over the course of the loan.

Loan Period (in months): The number of months the loan will be active. (If you have it in years just multiply it by 12 to arrive at the number of months.)

Automatic Calculations

Monthly Payment: Calculated based on the total loan value (after down payment and trade-in value has been applied), the length of the loan, and the annual percentage rate.

Interest Paid: How much interest will be paid over the course of the loan.

Total Cost of Loan: The original loan value (minus any down payment or trade-in value noted) plus the interest paid over the course of the loan.

Assumptions

• The APR (Annual Percentage Rate) is fixed and does not change over the course of the loan period.

Track and depreciate (using straight-line or declining) everything you own over a certain dollar threshold in order to have a list for insurance in case of property damage / break-ins and to know how much insurance is needed to replace the items.

User Inputs

Depreciation Type: Choose whether to depreciate using a straight-line method (same absolute dollar amount gets reduced each year) or a declining method (uses a percentage rate instead of absolute dollars which results in larger depreciation up-front and lower amounts in later years).

Type: The categories you want to use to bucket your assets.

Useful Life: How long you expect something in said category to be useful for before needing replacing. This is not an exact science. You can also think about is as after X amount of years I have gotten my money’s worth from this item.

Year: In the yellow cells under the current year, enter the dollar value of the item.

Asset: The name of the item to include where it was purchased from.

Serial Number: The serial number or any other identifying number.

Automatic Calculations

Using the useful life along with the depreciation type, future years will be built out automatically to create the depreciation table.

Estimate whether it makes more sense for you to buy or rent in your current situation.

User Inputs

In progress.

Automatic Calculations

In progress.

For when you need to compare the financial drain of different locations you’re thinking about renting from.

User Inputs

Start Month: The month the lease will take effect.

Annual Rent Increase %: An estimate of how much you think the lease holder will increase rent by for a renewal.

Annual Income: The annual income for both individuals in the household. (Be sure to set the names and initials in the Input sheet.)

Rent Payment Distribution: Allocate who pays what percentage of rent.

Location 1,2,3,4,5: Change these to the names of locations you are considering (addresses, complex names, etc.).

Automatic Calculations

Most of the calculations are self-explanatory. Note that the data is split into two sections (only comparing rent and comparing rent with concessions).

This tracker determines how long it takes for the rewards points to cover the annual fee on a given credit card.

User Inputs

Points per $: How many points you receive per dollar spent (on average).

Month Points/Spend: For each month and card, provide either the points earned or dollars spent during the time-frame. Do not provide both for the same card and month otherwise it will double count.

Redemption Multiplier: The value of a point when redeemed (i.e. points from the Chase Sapphire Preferred are worth 1.25x when redeemed towards travel).

Annual Fee: The combined annual fees associated with the cards.

Automatic Calculations

Dollar Value per Point: Uses the points per dollar and redemption multiplier to calculate how much (in dollars) one point is worth.

Points Redemption Value: Sums all of the points/spend year-to-date and converts it to a redemption value using the redemption multiplier.

Points Dollar Value: Converts the points redemption value to a dollar value.

Card Specific Total: Shows the total redemption dollar value earned from each card.

Gain (loss): Based on all of the data, this field calculates a net gain/loss to help you see how long it takes to make the credit card annual fee(s) worth it.

Callouts

• For a given month, only enter points or spend for each card (otherwise it will double count the value-add).

This tool allows you to keep track of your side income and watch it change year-over-year.

User Inputs

Start Date: The date the work started.

End Date: The date the work ended.

Hours: The hours worked during the specified time period.

Rate: The hourly rate. (If the job was not paid hourly you can back into an hourly rate based on how many hours the job took.)

Client: Who the job was for / what the job was.

Side Hustle: What side hustle the work was for (in case you have multiple side hustles).

Automatic Calculations

Year: Calculated based on the end date.

Paycheck: Calculated as the number of hours worked multiplied by the hourly rate.

Use this tool to estimate your annual income tax liability. Since taxes are complex this may be slightly off versus your actual numbers.

User Inputs

Filing status: How you will be filing your taxes (single, married filing jointly, married filing separately, or head of household). This dictates the income tax bracket thresholds and how much you may owe.

Annual gross subject to tax: The amount of gross income you make on an annual basis that can be taxed (i.e. your annual salary before tax).

Itemized deduction (if more than standard deduction): If you are unable to take the standard deduction, or your itemized deductions are higher than the standard deduction, you should input how much the itemized deduction is here.

Qualified business income deduction: Deduct any qualified business income here.

Automatic Calculations

Standard deduction: This is calculated based on your filing status and is used if higher than the number in the itemized deduction cell.

Taxable income: Your taxable income minus any deductions.

Income tax before credits: How much tax is being paid after summing across all applicable brackets.

Effective federal income tax rate (tax/gross): How much tax is being paid relative to your taxable income.

Expirations: Keep track of when your cards, vehicle registration, memberships, etc. expire and how much the cost of renewing (if applicable) will be. For recurring annual expirations (i.e. vehicle registration) the formula to have the date roll-forward automatically is:

=IF(TODAY()>DATE(YEAR(TODAY()),12,31),DATE(YEAR(TODAY())+1,12,31),DATE(YEAR(TODAY()),12,31))

Just be sure to change the month (12) and day (31) to the current values for the applicable expiration.

Events: Keep track of important annual events here in order to see everything at-a-glance.

Emergency Fund: An easy way to back into how much of an emergency fund you should maintain is by taking your average monthly expenses and multiplying it by the number of months you would need to live off of it. It is commonly suggested to have between 6-12 months; it just depends how quickly a new income source can be established after losing the previous one.