Spreadsheet Tools for Personal Finance
I often talk about using spreadsheets and how I quickly code up amortization charts and other tools in order to determine different financial data. Sometimes, I’ll give information on how to work a function into a spreadsheet I’m using, but I rarely provide the actual spreadsheets themselves. The reason I don’t is because the spreadsheets are much too customized to my needs to really do anyone else any good. In order to alleviate this deficiency, I will occasionally strip out personal information and provide base spreadsheets you can further customize to meet your own needs. Sometimes these will have been referenced in a previous article and sometimes they’re just tools I’m using in order to determine private needs and making assessments for my future.
I’m a very numbers oriented person and considering my Computer Science background, coding spreadsheets using Microsoft Excel (or OpenOffice’s Calc program) is irresistible. So, below are some spreadsheets and basic explanations. Each spreadsheet could be used as-is with no further customization. Simply enter your data and view your result. However, if you do customize them, please let us know what you did in the comments section. Maybe it’s an idea I’d like to integrate myself, but hadn’t thought of!
Please feel free to distribute and modify these spreadsheets. Just leave in credits to the original author.
This spreadsheet uses various factors including projected interest rates, home sales, and current expenses to determine what value of home you can afford. This defers to a pre-qualification which will tell you how much a bank is willing to loan you. This tool is intended to utilize worst case scenarios in order to minimize loss and potential bad credit and debt. More information is available in the ‘Notes & Instructions’ tab of the worksheet.
This is a pretty simple spreadsheet which I use to track my credit to debt ratios. This credit to debt ratio is used as a major factor when determining your FICO score. It should be a goal to keep your ratio to under 50% of your available credit. The lower your credit to debt ratio, the better FICO score you will receive.
This spreadsheet is best utilized when you regularly retrieve your free annual credit report. The best way to regular monitor is to evenly withdraw one report from one agency at regular intervals. Since some states allow for more than one retrieval per year, this would allow a resident of Massachusetts to retrieve one report every two months. Most states do not have this ability and you would retrieve one report from one agency every four months.
Car Loan Affordability Spreadsheet
This identifies based on the monthly payment you indicate you can afford, what the best of three styles of auto loans might work best for you. The three styles are as follows and each discount fees such as title, registration, tax and insurance down-payment as these should not be included in the loan amount:
- Full Amortization - No down-payment made, and the full value of the car is paid via a loan.
- Extra Payment - No down-payment is made, but immediately after purchasing the vehicle, an extra, large payment is sent to the lender (the equivalent of what the down-payment could be). This will not reduce your monthly payment but will reduce the total interest paid and result in the loan being paid off sooner.
- Loan - Downpayment - A down-payment is made and the remainder is lent.
The tool then figures out which of the three best meets your needs and what you can afford. This is displayed on the top of the details page. Each individual worksheet also shows the total interest you’ll pay over the course of a loan. The Extra Payment worksheet will show you how much interest you’ll save and the time you’ll take off the loan with an extra payment.
This worksheet was most useful to take to the dealerships using my Palm and Documents to Go. I was able to plug in the interest rate as the dealer offered a loan and determine what the best course of action would be.
Loan Amortization Table Spreadsheet
Last, but not least, is the Loan Amortization Table Spreadsheet. I created this one because I know how to do it so quickly nowadays, I just randomly open a blank spreadsheet to write it without bothering to save it in most instances.
This spreadsheet can be used to figure out how much principle and interest you’re paying for any kind of loan. I have it running up to 40 years of time frame with monthly payments, but this can be customized to run longer with very little effort by simply dragging the formulas down in each column.
When using this spreadsheet to determine payments, please be aware many loans have extras you have to pay, especially mortgages. Mortgages often also include an additional payment to an escrow account from which taxes, insurance and PMI are paid out of. Many mortgages require this escrow be taken (and yes, they do give you interest on the funds).
I hope you found these spreadsheets as useful as I do when running the numbers and trying to determine your finances.
Popularity: 41% [?]
Related Posts:
May 24th, 2006 at 3:30 pm
I love the geek factor involved in all of this, especially your credit to debt ratios spreadsheet.
May 24th, 2006 at 3:33 pm
Wait until I get around to universaling the spreadsheets I use to determine credit card debt payoff.
April 28th, 2008 at 11:39 am
On the Home Affordability Estimator, where do you put in your current Expenses. It looks like the only info it is requesting is if you have a current home that you are selling and there’s no place for you to put in your income. I don’t have a house, but I do have some regular monthly expenses and an income. Is there a spreadsheet like that?
I’d like to be able to put in my wife’s and my income, my current monthly expenses (including groceries, Utilities, and everything else), savings, etc. and come up with a figure of what I could afford that way.
I know that is not typically the way it is done. But I’d like to be able to have my life figured out first, my house payment factored in after that. And once the monthly payment is figured out I’d like that translated into what is the max home price I could afford.
Just general over estimated figures.
Any chance of a spreadsheet like that?
April 28th, 2008 at 1:48 pm
I wrote it assuming you know how much you have to spend (budgeting it something else) on housing. General rule of thumb is to not spend more than 25% of your gross income on housing (rent or mortgage).
The rates also assume a fixed rate mortgage, not an ARM.