Financial Analysis of LifeProvides a complete Financial Analysis of your Life. It works for both Dual / Single income Families and Single people.
Takes into account:
- Dual or Single Income Families
- Payroll Income and Non Payroll Income
- Your Expenses
- Federal and State Taxes Filing statuses
- Owning or Renting a Home
- Home Loan Qualification
Provides Financial Summary Reports for:
- Total Federal and State Income Taxes for the Year
- Total Expenses for the Year
- Payroll Taxes for the Year
- Home Loan Summary
- Tax Comparison to Owning a Home versus Renting
- Payroll Calculations
- Home Loan Qualification Section
- Home Loan Amortization Table with Tax Credit
- Credit Card Calculations using the same methods as the financial institutions
See the bottom of this page for a screen shot of the main Summary sheet.
This program was designed to provide a complete financial analysis for a user's life style. It will calculate two simultaneous situations at the same time. The first one for a Renter's situation and the second for a Home Owner's situation.
Note: The Tax / Payroll tables used in the program were initially set for the years 1998 / 1997, but can be easily updated / changed to any year you desire.
When you first opened up this spreadsheet program, you may have noticed that both the Summary and Expense sheets have been initially filled out. This sample data represents a family that owns a home and has a two person income. Examine this sample situation so that you may get familiar with the operation program.
This workbook the contain six (6) spreadsheets as stated below:
User interface sheets: Summary, Expense, and Credit
Support Sheets: Payroll, Loan, and Taxes
To utilize this program , you only need to add / modify the data on the Summary, Expense, and Credit Sheets. All other sheets within this program are support sheets. The data you enter within these sheets are automatically transferred to the support sheets.
If you need to update any of the Support sheets with the current year's values, then follow the color conventions outlined below. Keep in mind that updating the Support sheets with the current year's values will not significantly change the outcome of the results. This is only mentioned to save you some time if you do not need exact dollar accuracy.
The program was designed to give accurate results for an average middle class user. Due to constant changes in the tax laws, the results presented here may not actually represent an accurate view of your situation. Due to certain tax codes, it does not accurately represent extreme end situations such as users with excessively high incomes and multiple investments or for poverty level incomes with welfare assistance. Use this program as a guide for general information. Your situation may vary beyond the parameters designed within this program.
By using this program, you willfully agree and acknowledge that Sam Raheb, the developer of this program, cannot be held responsible in any way for any actions taken with the use of the information obtained from this program.
The following programs are incorporated into the analysis:
Employer Payroll Program - Based on the 1998 California Withholding Schedules
Home Loan Amortization Table with Tax Credit
Both Federal and California State Taxes - Based on the 1997 Tax Schedules
Additionally, a separate Credit Card Program has been provided within this program. It is a individual stand alone application without any links or associations to other sheets within this workbook. Use it as a tool to calculate monthly interest and payments which you may want to include in the Annual Expense Table.
If you do not live in California, you can still use this program. Both the Payroll program and California State Tax programs should be very similar to other state methods. You can however change the values in the both the Payroll and California State Tax tables to reflect your state's methods.
This program was designed in such a way to allow the user the option to make changes to the Payroll, Federal, and State Tax tables. In future years, you may want to update these tables to reflect the changes in both the payroll and the tax systems.
You can obtain up to date payroll and tax information from the following government web sites:
IRS Federal Tax Tables and forms: http://www.irs.ustreas.gov/
California Franchise Tax Board State Tax Tables: http://www.ftb.ca.gov/
EDD California Payroll Information: http://www.edd.cahwnet.gov/
In order to do a direct comparison between renting and owning a home, the value for the house payment is the same for both the Renter's and Home Owner's situation. This value is calculated on the Summary sheet under the Home Loan Summary section, line item "Periodic Payment". This payment value is also linked to the Expense sheet. Do not over write this value on the Expense sheet.
If you rent, then change the price of the home to get the value of the "Periodic Payment" equal to the rent you pay. Use values that would represent a real situation of owning a home. This way you can see the difference between renting and owning a home with the same periodic payment. Note that the tax advantages of owning a home will not apply to the renter. The tax advantage of owning a home is shown on the Summary sheet under the Home Loan Summary section, line item "Mortgage and property tax refund credits per period".
You can use the Goal Seek routine to obtain an exact rent or home owner periodic payment value. See below for the Goal Seek explanation.
Color Conventions (Very Important)
Font Color Conventions used throughout this program
Black = General Text - Labels - No need to edit these values.
Red = User input cells - You can change the data within these cells.
Magenta = Contain Formulas - Do not edit or overwrite these cells.
Blue = Contain Formulas - Do not edit or overwrite these cells.
Due to the complexity of the program, do not do any of the following:
Delete or erase any of the cells
Delete or rename any of the sheets
Rearrange the order or format of the cells
Just use the program as is. You may change the values in the payroll and tax tables to keep the program up to date year after year. Remember to only enter data into the red colored font cells.
To provide the best and most accurate view of your situation, assume that the financial analysis starts at the beginning of the year. The program was designed to analyze a one year period. Keep in mind even though you may be using this program in the middle of the year, that the program calculates the Income taxes for the entire year.
The tax and payroll tables in this program are set currently for the year 1998. If you use this program in a future year and do not want to update the tax and payroll tables for the present year, then the values calculated will only be slightly off for the present new year. In other words, you can use the present 1998 situation for analyzing your financial situation in future years and feel confident about the results if the changes in the tax tables from year to year are small.
1. Fill in the Annual Expense Table on the Expense sheet.
2. Fill out the Family Profile and Income sections on the Summary sheet.
3. Fill out the rest of the red colored font cells on the Summary sheet for your particular situation.
In other words, just overwrite the sample data in the red colored font cells with data that represents your family situation.
Goal Seek Routine
The best feature of this program is the Goal Seek routine as shown in the image below. This routine will let you determine any desired result in the blue colored font cells by automatically changing a value in any of the user red colored font cells.
For Example: You can determine the amount of a home loan you may need in order to pay exactly $1000 / month after taxes.
Home Loan Qualification Section
If the calculations seem to slow on your machine and you are not interested in the Home Loan Qualification section, you can temporally turn off the updating of this section by selecting the "Do Not Update option button on the Summary sheet.
Credit Card Sheet
Use this spreadsheet program to manage all your credit card accounts using the same methods as the financial institutions. The program will allow the user keep track of the debits, credits, financial charges, minimum payments, and the average daily balance of an account. The user can also duplicate (copy) the main sheet called "Credit" to other sheets in the same workbook to manage additional accounts.
Allows future grace periods if full payment is received.
Automatically adjusts for over payments made to the account.
Rounds the results to the nearest penny ($0.01).
Prevents the user from accidentally overwriting crucial formulas.
Note: Remove the sample data before using.
Only enter values in red colored font cells and in the Debit/Credit Columns (Payments must be entered as negative numbers).
The first month is the initial billing cycle; The second month is the grace period, no interest is charged if the balance is paid off in full.
The" Minimum Payment Amortization Years" value is the number of years used to calculate the rounded off minimum payment.
If the Billing Cycle ends on a weekend, then the billing cycle is extended to the following business day which is Monday. If this particular case does not apply to your situation, then uncheck the box labeled: "Extend Billing Cycle to Monday if ends on a Weekend".
If you see the value "#NAME" displayed in the cells after you make a change to the initial sheet, then you must load the Add-in called "Analysis ToolPak" into Excel so that Excel will be able to calculate the special date functions used within this program. For Excel version 5.0, you may also need to load the Add-in called: "MS Excel 4.0 Add-in Functions" in addition to the Analysis ToolPak.
Enabling the Analysis ToolPak
1. On the menu bar, choose the Tools menu and click Add-Ins. If Analysis ToolPak is not listed in the Add-Ins dialog box, click the Browse button and locate the drive, directory, and filename for the Analysis ToolPak add-in or run the Setup program.
2. Select the Analysis ToolPak check box, then press the OK button to install.
3. After doing this press the F9 key to recalculate the spreadsheet or else change a value in a cell and press the Enter key to recalculate the sheet.
Note: The Add-ins you install in Microsoft Excel will remain active until you remove them. If the Add-in function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must select and enable it in the Add-In Manager.
Screen shot of the main Summary sheet.
Click here to see a bigger clearer image.