If you have a real estate property, you may have considered refinancing your loan to lower your monthly payment, lower your interest rate, or reduce other mortgage fees. This spreadsheet is a great way to estimate and understand all the numbers you’re going to see when you sign the closing papers on your refinanced loan.
As with our Buy a Home StencilBook, this StencilBook is meant to evolve over time as you understand more about your loan terms. It doesn’t require a lot of set up but if you want to take advantage of all the features created for you, read on.
- Product tour
Let’s start by reviewing the tabs and important sections in this template:
Current Loan - in this tab, you’ll enter the numbers that compose details of your property’s
- The top of the page has the
key metricsthat are important when comparing your existing loan to a new loan. These numbers will change based on what you enter below.
categoriesselection box shows the four major categories you can use to add detail to your estimate.
- The table below is where you’ll dive into the values you need for each Category. you’ll enter the values you know. Don’t worry about knowing all the values - these are optional depending on your needs.
- The top of the page has the
- Refinance Loan - here is where you’ll enter the numbers your anticipating for your new loan. This tab looks similar to the Current Loan tab, but you’ll see a section to the right. This section shows how the numbers you’ve entered for the
refinance loancompare against the
- Amortization_CurrentLoan - if you want dive into the details of each monthly payment towards your
current loan, this tab has all the nitty gritty information.
- Amortization_RefinanceLoan - this tab has all of the details for each monthly payment for the
- Help - if you forget how to get back to these instructions, you want to know what updates have been made to this template, or you just want to see what else StencilBook is up to, this tab has all the links.
We would recommend you start setting up this spreadsheet by entering information on the Current Loan tab to get a clear understanding of the current finances, and compare the results of the calculation against your mortgage statements.
You can enter numbers in any of the white boxes that have a dotted underline. If you don’t know the exact values - no big deal - take your best guess. This StencilBook is meant to evolve over time as you understand more about your loan terms and what details are important to you. Continue going through each category adding what you know, skipping what you don’t.
Once you’ve completed the Current Loan tab and the estimated monthly payment at the top matches, or is close to, the actual mortgage payment you make, it’s time to look at your refinance options.
In the Refinance Loan tab, enter the values you’re anticipating for your new loan. Just like in the previous tab, add the details you know, skip the ones that you don’t know or don’t apply to you.
The following sections outline the features that will make it easier to fill out the loan terms for both the Current Loan and the Refinance Loan:
Any box with a tiny red triangle in the corner means there is a note we’ve put in for you. If you hover over it, the note will appear and give you guidance on what type of information or considerations to make when you are filling in that box.
If you click on the black information buttons, you’ll be navigated to a website with helpful information about that particular topic. In particular you’ll see many info buttons around taxes and fees because they can be very confusing. When researching this topic, we found a few articles that were incredibly helpful and wanted you to have them easily accessible to help you understand the home buying process.
As you’re entering values for each category on the Refinance Loan tab, you’ll notice the values to the right changing. This right-hand section of the table is to help you understand how changing the values - turning the knobs - will impact your
key metrics. It compares the results of the numbers you added for that category against the Current Loan terms.
So what if you don’t want to include paying mortgage points, taxes, or other categories in you estimate? You can use the selection box of
categories at the top of the page to hide those sections so you can focus on the categories that matter most to your decision. By clicking on the multi-select button, you can turn on and off different categories. If you want to show all of them again or none of them, click the filter button.
Keep in mind, by hiding a category using these buttons, it doesn’t remove them from the calculation. To remove values from a calculation you’ll need to delete the values in that categories’ boxes.
There are two spots in the Refinance Loan tab where we thought you may need a little extra help filling in the numbers. In the
Basic loan terms category, if you anticipate your new loan term will be for the exact same balance as your Current Loan (aka not planning a cash-out refinance), there is a drop down where we have calculated that value. Simply click on the cell where you’d enter the
New loan amount value, and you’ll see a small down arrow. If you click on the down arrow, you’ll see a number pop up (the calculated balance of your current loan) and by clicking on the number it will fill in that cell with the number in the drop-down.
Another use of this feature is in the
Reselling category. If you anticipate selling your property after the loan is paid off, you can enter the date of your last loan payment by using the drop down.
You can always overwrite both of these numbers and pull them back in using the drop downs if you want to play around and see how it will impact your
What even is amortization? It’s the way payments are spread out over time. Even though the amount of money you pay each month will be the same, different amounts will go to the principle versus the interest. If you want to know exactly (or as close as we can calculate - your bank may do it a tiny bit differently) how much of your payment goes to principle and how much goes to interest, go to the amortization tabs. There are separate amortization tables for the Current Loan and the Refinance Loan because the terms are likely to be different so the breakdown of payments will be different.
In this same table, we’ve also pulled in the values you entered for each category. If there is a certain month or subset of months you expect a change (for example, if you only want to make extra payments for a year instead of the entire lifetime of the loan), you can override the formulas in the table and see how that affects things. Keep in mind, changing numbers in the Amortization tabs will not change any numbers on the Current Loan or Refinance Loan tab. It’s like a one way street (numbers go in, but not out).
Run into something wonky? Check out these questions first.
This text will show up in a cell and it looks scary, but all it means is that the width of the cell isn’t big enough to show all the digits that are supposed to be visible. Simply make the column width larger by dragging and dropping the width. You can also highlight the column, right click on it, click Column width, and enter a new number for the column width.
When a spreadsheet is protected, it means that all or certain parts of it can’t be edited (unless you unprotect it). Most of this template has been protected to ensure you don’t change anything that doesn’t need to be changed, but you do have freedom to change specific things to use the template appropriately. The option to Protect is done on an individual sheet (aka tab) basis. If you look at the sheet name on the bottom left, you’ll see a little lock symbol next to the name showing that it is protected. Or maybe you got a pop-up when you tried to do something that said:
Alert: The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password
That’s because the sheet is protected.
To unprotect a sheet, check out the Excel ribbon at the top of the screen. Click the Review tab, then click Unprotect Sheet.That’s it! Now you’re free and clear to change anything on that sheet.We’d recommend you re-protect the sheet once you’ve made the changes you wanted by going back to the Review tab in the ribbon and clicking Protect Sheet (not to be confused with the Protect Workbook button - that’s totally different unfortunately).
You can make changes to the look and feel of the entire template file by changing the Theme. First, you’ll need to unprotect each sheet. Next, in the Excel ribbon, click on the Page Layout tab, then you can click Theme to change both the font and color scheme, or choose them independently using the Colors and Fonts buttons to the right. Don’t forget to re-protect each sheet
The notes for any updates that have been made to this product can be found in the Change Log.
Send an email to email@example.com