This mortgage calculator will allow you to accomplish three different things:
1) Determine the housing price based on a desired monthly payment, interest rate, and terms.
2) Determine the monthly payment based on the price of a house, interest rate, terms, and down payment
3) Calculate the mortgage balance remaining and calculate a gain or loss depending on the selling price you enter.
To select which calculation you are after selecting the option from the drop-down under the ‘Calculate’ header:
All of the inputs are in yellow cells and can all be changed. Please note that since some of the same fields are used in the different calculators, if you input figures in one cell and change the calculator you will notice the fields won’t reset to 0 but will carry the figures you entered earlier. So you will need to clear or change the amounts in those fields when that happens.
Calculate an Affordable Housing Price
I’ll start with the first example, the Housing Price. In this instance, you want to determine the house price you can afford based on the annual interest rate, term, and how much you want to pay per month. Once you enter your inputs it will tell you the house price that you can afford and if you need additional funds for your downpayment – based on your desired downpayment %
In the above example, I wanted to know what price I can afford if I wanted to pay $2,000 a month at a 4% annual interest rate for 30 years. The result was $523,653.10. And since I entered my desired downpayment % as well as how much I had available, it lets me know any amounts I am short for the downpayment. For a house costing $523,653.10, a 20% downpayment would be $104,730.62. Since I put that I have $100,000 available, it tells me that I need an additional $4,730.62 to meet the 20% downpayment.
Calculate the Monthly Mortgage Payment
Next, I will switch over to calculate my monthly payment. In this situation, I specify the house price I want to buy, the amount of the down payment, interest rate, and years
In this example I set my house price to $500,000; downpayment to $50,000; term again 30 years and interest rate also still 4%. This calculates my monthly payment to be $2,148.37.
Calculate the Gain or Loss on a Sale Price
In my last example, Gain/Loss on Sale has the most variables since it takes into account the price, down payment, monthly payment, the start of the mortgage, selling date, price, and estimated costs. This would be if you wanted to gauge whether you might be looking at a loss or a profit based on the variables entered.
If you’ve been entering these amounts in the previous calculators you will notice some values have carried over, so always be careful to clear the fields first or at least double check the inputs or you may get a result you did not expect.
For my inputs here I have entered similar values to my earlier examples and now I am calculating whether I will have a gain on the sale if I sell it for $550,000. After commission costs of 20,500 plus closing costs of 11,000 (550,000 x 2%), plus the mortgage balance that remains of 404,966.58, that will leave me with proceeds from the sale of $113,533.42. The gain or loss in this template looks at whether I am walking away with more money than my original downpayment or less. Since my downpayment was $104,730.62, it is a gain on the sale since I am taking more than what I originally put into the house.
These calculators should only be used for estimating purposes and shouldn’t be intended to calculate with 100% accuracy any tax liabilities or other costs. Housing rules vary widely from one region to another so it would be very difficult to factor in every variable. Even mortgage penalties among banks vary in calculation so for the sake of simplicity those variables are taken out of the equation, however, you can estimate a closing cost % and if you want to be conservative you can adjust this % to help account for these variables as you see fit.
This template allows you to track multiple amortization / depreciation schedules and summarize them all in one tab. Above is a completed schedule with the inputs (highlighted in grey at the top) filled in. As you can see the last payment will also take care of any balloon payment required. I have made 10 different tabs but you can copy additional ones or delete ones you do not need. Each schedule accommodates up to 1,000 payments by default and assumes monthly payment intervals however this can be adjusted.
In the sample file I have three different amortization schedules. If I go to the Summary tab I see the following break:
The tab names must match to what is written in the Tab Name field otherwise they will not pull correctly. If you update the Current Month field (just enter a date value, do not enter text even though it looks like text) the formulas will update and show you what your balance currently is, how much interest has been paid to date, and how many payments are still remaining. The benefit of this template is if you are managing many different amortization schedules you can get a snapshot of all of them in one tab.
This is an updated version of a prior post that generated a statement without VBA, but the limitation there was you had to save the pdf yourself. This updated version allows you to do the following:
– Save the current statement to PDF
– Cycle through all your customers and save all their statements to PDF
– Generate an email (but not send) to the customer with the attached statement.
It is important to note the customer name on the invoice data needs to match the name on the customer data tab otherwise the invoices will not pull on the statement correctly.
The template works in exactly the same way as the prior version – invoice data and customer data needs to be downloaded from your accounting software. You can customize your statement and include any images you like so that it will look consistent for every statement that you send out.
This template is setup to accommodate up to 150 invoices.
In this template you can generate a chart showing the history of a customer’s aged accounts receivable. This chart will show a breakdown by invoice age so it will be able to tell you a great deal in one picture: the customer’s total receivables by month, breakdown of the age of the receivables by month, how much sales is being done with the customer (this would be the current receivables), and whether the receivables are growing or declining. It could be a very useful tool in evaluating a customer’s credit worthiness and in helping detect potential problems.
The main input tab is the AllTransactions tab, columns A:E. Column D specifies the type of transaction and should either be PAYMENT or INVOICE. Column C (Date) relates to the date of the transaction – either payment date or an invoice date. Columns F:H are formulas.
The other input is the Customers tab. You will need to enter all the customers onto here. The easiest way would be to copy the names from all transactions and just extracting unique value (see this post on how to do that). Note that the customer names here must match the names on the AllTransactions tab otherwise when you select a customer data may not populate correctly if the transaction data does not have a match for that customer name.
Once entered, you can go to the Aging Chart tab and select your customer from the drop-down menu and the chart will update:
It is a stacked column chart so in addition to just seeing overall receivables by month you can see their age makeup. This customer did not go past over 30 days so they don’t venture past the dark green shading. Now, my other customer, Bad Customer, has a lot more colour:
This customer has gone as high as 120+ so they have the full spectrum of the aging schedule on here. The closer the colour is to red, the older the receivable is. You can modify these colours to your liking.
The current period that I have the chart running for is from January 2016 until March 2017. You can change the starting period in cell B2 on the Summary tab and if you want to add more months then simply drag the last column’s cells from rows 1 to 8 into the next column so that the formulas will update.
Because there are no macros in this template, you will also need to update the chart range so that it includes the new months you have added. To do so, right-click on the chart and click select data and in the chart data range enter ChartData – this is a named range that will automatically select the furthest column.
After you hit OK the chart will update. If you delete columns you don’t need to re-size the chart, this step is only needed when adding additional columns and months
Tracking employee vacation and time-off requests can be a bit of a headache. This template will help track time off as well as how much vacation has been used up and how much is remaining. It also helps to prepare your recurring journal entries to expense vacation.
This template will help you track and reconcile vacation liability owed to employees. It will calculate this both in hours accrued and taken, and in dollar values if you need to as well.
The first tab is the EmployeeInfo tab which is where you will need to enter your employee information. The Per Day $ and Vacation Rate fields are automatically calculated. Everything else, you’ll need to enter in. In the below example I’ve filled in some sample employee information:
You can replace the data in the table and you can also add rows by just typing the employee name in the next blank row in column A and the table will automatically re-size.
If you do not want to track dollars accrued then you can simply leave the wage field blank. This template will allow you to track vacation dollars and hours even if there are wage or vacation rate changes. In the above example, Jack Smith had a wage of $10 from January 1 until May 31. And from June 1st his wage changed to $15 and vacation rate increased from 4% to 6%. I’ll go over this calculation later in this post to show you that it has calculated properly in the reconciliation tab.
If there are no rate changes you can leave the end date field blank. You only need to use the end date field if the employee has a change in vacation days or wages, or is no longer with the company. In the latter case you will also want to set their status to Inactive. Doing so will exclude them from the JE tab and calculation.
The next section is to ensure the calculation is done correctly and the right percentages are used – daily hours will likely stay as 8 and the annual hours to used in the vacation rate calculation. So if you are paying an employee 4% vacation that is equivalent to 10 vacation days based on 2,000 hours.
Also on this tab is a section to enter the holidays for the year, and this is to ensure that the vacation calculation ignores these non-working days.
The cutoff date is up to what date you want to reconcile to. So if you wanted to know what the vacation liability looks like at the end of the year you would set it to Dec 31, as I have below.
The next tab is the TimeOff tab. This is where you can select when the employee took vacation, and I added an option for sick as well in case you wanted to track that as well.
In the above example, John Burns is away January 3rd, 4th, and 5th, so I enter the 3rd as the start date, and the 5th as the end date. The next business day would be the day he is back at work. The hours taken reflects the average daily hours from the previous tab. The wage information also comes from that tab.
On the Calendar tab you will see a year-long calendar off the time off. This isn’t part of the vacation calculation but allows you to visually track vacation and sick time taken.
You see above that the days for January 3,4, and 5 are highlighted for John Burns to indicate he took vacation on these days. Sick days will show in a different colour. Weekends and holidays are also filled in with light grey. This would be more of a scheduling tool when you are dealing with many employees and wanted to minimize time off conflicts and overlaps.
The Reconciliation tab shows a summary of all of the time earned and vacation time taken off.
You can enter a starting balance if you carry forward vacation from the previous year, and adjustments for any items not captured in the hours earned or time off taken (e.g. vacation pay out). The other cells are formulas and should not be modified.
Verifying the Vacation Reconciliation Calculations
I will refer to my example above where Jack Smith had a change in wage and vacation rate. His wage won’t affect this calculation but his vacation rate will.
From January 1 to May 31 he will accrue at a rate of 4%. Since he works 8 hours a day x 106 working days which fall within this range once holidays are factored in, that is a total of 848 working hours. 4% of this total is 33.92 vacation hours.
The second range is from June 1 until the end of the year (you can adjust the cut-off date in the EmployeeInfo tab). During this range there are 145 working days x 8 hours for a total of 1,160 total hours worked. At his new rate of 6% this is 69.60.
If I total 33.92and 69.60 that gives me 103.52 hours earned which is what the reconciliation shows.
If you track dollars there is a separate section for the dollars accrued which works in the same way.
Again, going back to my example earlier, Jack Smith had a $10 wage from January 1 to May 31st at a 4% vacation rate. If I multiply 106 working days by his daily pay (daily hours of 8 x wage of $10 = $80 per day) then I arrive at 106 x 80 = $8,480. And 4% of this is $339.20.
Now from June 1st to Dec 31st that is 145 working days x his new daily rate of $120 ($15 wage x 8 daily hours) is a total wage of 17,400. And 6% of 17,400 is $1,044.00.
If I add the two amounts together, I get $1,383.20 (1,044.00 + 339.20). As you see this equals the dollars earned for Jack Smith.
Vacation Accrual Journal Entry
The last tab is the JE tab which is used to generate your vacation accrual journal entry.
You will need to specify the GL accounts, pay period, start, and end dates. There is also a space where you can put an adjustment for an employee. For instance, your accrual might be for a two week period but if someone was terminated or shouldn’t have accrued for any period of time here is where you can make the adjustment.
The employees listed as active will show up in this list as well as their wage during the dates you enter – as long as you don’t have rate changes that occur in the middle of a pay period there should be no issues here.
Please note this spreadsheet should be used to help reconcile to your payroll provider’s data to ensure accuracy and completeness. Do not use this spreadsheet alone in determining your vacation liabilities as I do not offer any guarantees as to its accuracy.
Download the Vacation Accrual Template
Free version: Download Here. The free version is limited to 4 spots for employees on the EmployeeInfo tab and the sheets are locked and there is an ad in the Excel ribbon.
For the full version (no ads, locks, or limits to # of employees) click on the following button:
It is fairly straightforward – you select the team you expect to win in each bracket and the number of games. The page can print out easily into one page but the results are also stored in a more data-friendly version in the Results tab.
On the standings tab is where you will find the seeding of the playoff teams. This is important for the purpose of re-seeding the teams that remain in the semifinals.
Once you get to the semifinals, make sure to click the Re-Seed Semifinals button so that the correct opponents are facing each other. If you want to start over, click on the Reset Selections button.
PLEASE NOTE THERE IS A NEWER VERSION OF THIS TEMPLATE AVAILABLE HERE
A bank reconciliation can be a time-consuming process but this template can help speed it up. By assigning categories and setup quick ways to match items, you can quickly accelerate the reconciliation process. With an easy interface to match transactions, this template should help speed up what can otherwise be a tedious process.
Download Options
This version has been discontinued. Instead, please refer either to the 2020 or 2023 versions.
Note on #NAME? Errors
If you see this error message in the spreadsheet it means you have not enabled macros when opening the file or the calculations have not been able to update. If you think macros have been enabled and updated calculations but don’t see the results updated, then just try re-opening the file and that should fix the issue.
How to Use This Template
This template allows you to easily reconcile your bank to your book by 1) categorizing transactions, and 2) reconciling any matches based on those categories.
Setting up Categories to Match Transactions
In order to categorize transactions, you will need to use the Setup tab to first create the rules that will find and identify categories. The setup tab has five columns: Category, Identifier, Key, Length of Key, and Gap.
The Category column is simply the name of the category (e.g. wire transfers, deposits, cheques).
The Identifier is what needs to be in your transaction description to be flagged as belonging to this category. For example, suppose the following description on your bank statement indicates cheque # 1234 was cashed:
C#1234
In the above example you would want to set C# as your identifier since that is how you can identify this is a cheque and should belong to the cheque category. The category can have multiple rules (for example, the way a cheque is indicated on your bank is likely not the same as on your general ledger)
The next column, the Key, is what should be used to try and match and reconcile these types of transactions. This is a drop-down selection as there are multiple options: use date, use letters after identifier, use numbers after identifier, and use alphanumeric string after identifier. You can also leave this blank if you don’t want the template to try and automatically reconcile these transactions or if there is no discernible key that can help identify the transaction.
To use the date will mean just the date is used, nothing else. In the case of a cheque this will not work since you might have multiple cheques deposited in a single day, so to just look at date will not correctly reconcile these types of transactions.
In the case of cheques you will want to select ‘Use numbers after identifier’ since that will pull only numbers and not any text. If you need text you can select the option for ‘Use letters after identifier’, or if you need both numbers and letters – ‘Use alphanumeric string after identifier’
The Length of Key column is how long you want the key to be. Suppose the following description:
C#12340000000
Although the cheque number is 1234 the description leaves trailing numbers afterward or might have some other detail afterward that doesn’t pertain to the cheque number. In this case you will not want every number, but just the first four.
The Gap column is used if you don’t want to start retrieving text or numbers after the identifier. For example:
C#001234
In the above example there are two zeros before the cheque number. In such a situation I would set the gap to 2. This would skip the first two characters after the identifier and then start pulling numbers or text after that.
In my example image above I set the identifier to C#, a key length of 4, and a gap of 2. Here is some sample data and how it extracts the key field based on the rules I set out above:
In the first row there was no space between the identifier and the cheque number, as a result, my key only pulled the last two numbers.
In the second row there was one space, so only three numbers were pulled.
In the third row there were two spaces and as a result all four numbers were correctly extracted into the key field.
In the fourth row I added trailing 0s after the cheque number but since my length is set to 4, it does not include those extra numbers.
In the fifth and six rows I show that whether it is numbers or letters before the cheque number is irrelevant since the gap is set to 2. Now in the case of row six, I would not need to set a gap because if I am only looking at numbers it would have skipped the letters anyway.
Note that these rules are not specific to either the book or bank tabs; any rules will be applied to both. However, in all likelihood you would need to setup rules for the same type of transaction multiple times since the way your book shows a type of transaction is probably not the same way your bank will.
If for whatever reason there is no consistency in how some of your transactions appear you could leave the details on the setup tab for the key as blank, that way you still can categorize the transactions (if there is an Identifier) but with nothing set for the key the template won’t match any of those transactions for you, which brings me to the next part: reconciling the data.
Matching Transactions
In order to reconcile that data you will of course need to download your transaction details into the ‘Bank’ and ‘Book’ tabs. Columns A:D are highlighted in yellow and those are the only ones you need to fill in. They include fields for Date, Description, Debit, and Credit. The Description field is the field that will be used for finding what category a transaction belongs to and extracting the key. Columns E:H are formulas and are pulled from columns A:D.
In order for the template to match and reconcile items, it will look at the following: – Are the categories the same? – Do the keys match? – Do the amounts match?
If all three criteria match, then the Status column will show a ‘-‘ indicating 0; that the amounts are reconciled. Otherwise it will show a value of ‘O/S’
There is also column I, Manual Override. If you mark an ‘X’ in this field, it will make transaction marked as reconciled, regardless of whether the template finds a match or not.
In the above screenshot the second row is reconciled once I enter an ‘X’ in the Manual Override column. Any reconciled items will highlight in green.
Alternative, you you can select a line item that has a status of O/S and click CTRL+SHIFT+X which will bring up the Reconciler window which will show you potential matches for the item you are looking to reconcile.
In the above example I pressed the shortcut keys while selecting the row with the $5,000 wire transfer. Since it is O/S it pulls up the Reconciler window. The Reconciler shows all the wire transfers from both the previous O/S tab as well as the Bank tab (in this example I was on the Book tab). Exact dollar matches show up at the top. Note the first result shows an amount of -$5,000 – the negative does not indicate a credit, it pulls the amount from the amount field which just shows the negative as being an outflow of cash. Because an outflow of cash on the bank is a debit, if I select the -$5,000 it will show $5,000 DR as being the amount matched, which is shown on the next screen:
Because the debits match the credits, the Match Selections button has turned green and can now be pressed. Doing so will enter an ‘X’ in the manual override field for these amounts. You will only be able to match the selections if the debits and credits match. You can select multiple items if they add up to the total outstanding.
Completing the Bank Reconciliation
Once all the bank and book data are entered and you have finished matching which items you can, go to the Reconciliation tab where there you will see three buttons on the right hand side:
The Reconcile button will generate the reconciliation and show you which items are outstanding. It will group the outstanding items by category – refer to the screenshot at the beginning of the post. If the adjusted balances match and the amounts reconcile to 0 then it will highlight that line in green, otherwise it will be in red to indicate a variance. The reconciliation date you will need to enter in the yellow highlighted cell.
The New Month data will clear all your existing data but before doing so will put your existing O/S items into the Previous OS Items tab so on your next month’s reconciliation they can be used to match new transactions.
For example, if cheque #1111 does not clear this month it will be moved to that tab. If on next month’s transactions there is a transaction on the bank for this cheque number the template will be able to reconcile it automatically. Nothing extra needs to be done to do this, as when looking for matches, both the bank and book tabs look at the Previous OS Items tab to clear any items from there as well.
Lastly, the Clear Data button will do just that – get rid of everything from every tab.
The template at the top has some sample data so you can test out and see how it works.
This template allows you to create a customized letter – be it a cover letter or any other kind of letter from a pre-defined list of sentences and paragraphs that can also utilize variables. In order to accomplish this you will first have to setup the sentences, paragraphs, and variables you will want to use. But once you have done so you can easily reuse them going forward.
Setting Up Variables and Paragraphs
In my template I have two sections – one for variables, and one for paragraphs. In the variables section you can setup variables that can be used in your content. These will be values that will likely change from one letter to the next. In my example I have a % sign in front of the variable name. The purpose for this is to make it easier to accurately identify where a variable has been used. However you don’t need to use it, but you would want to ensure your variable name is unique and not a word that you might use in your content that you don’t intend to use as a variable.
In the paragraphs section here you can setup sentences or entire paragraphs that you would potentially like to add to your letter. The title is just a way to identify the content, the paragraph is which paragraph the content relates to. For example, I have multiple titles that relate to the paragraph called experience.
This means that if I add all of these items, they will be stitched together to form only one paragraph. This is just to help organize my paragraphs to make sure I don’t have a paragraph for each item I want to add. You may not have enough to write a paragraph about each skill set you have, but instead you may want to add it on to a paragraph that relates to the content.
If I look at the title labeled ‘Introduction’ this has a related paragraph by the same name. There are no other items that relate to the introduction paragraph. So this means if I add the Introduction, it will be an entire paragraph on its own, and no other items I add will be added to it. The Introduction also has all three variables I setup – %HIRINGMANAGER, %POSITION, and %COMPANY. These are all variables that you would expect to change from one cover letter to the next. I can certainly use more variables and put them into other content but in this example I only did it in the Introduction.
If you need to add more rows you certainly can do that, as well as changing the existing content, titles, and related paragraphs. If you want to add lines within a cell, all you have to do is when you are editing in the cell click ALT+Enter and you will create an additional line of text within that cell. This will allow you to help space out your descriptions as you wish.
Updating Variables
Once your paragraphs and variables are setup, click on the Create Letter button. You will first be prompted to enter values for your variables. Here I will update the name of the hiring manager I’m applying to, the name of the company, and position.
Once done, click Update Variables.
Finalizing the Letter
Next, you can select the content you wish to add to your letter. The titles will appear in the drop down boxes. Go from top to bottom. As you select an item it will be added to your letter.
When I add Introduction my variables are now updated to reflect what I entered at the earlier screen. You can also edit the letter in the preview box.
However should you make any changes to the drop downs your edits will be gone. So make sure to do edits only after you’ve selected all the content you wish to use.
Once you are done, click on the Copy to Clipboard button and you can now paste it into a word processing document, email, or wherever you like.
This is a chart that is useful in reviewing variances and monitoring change from one period to another. Favourable (positive) variances are green, and unfavourable (negative) variances are red. In this example I used a statement of cash flow. Increases or inflows in cash are favourable, while decreases or outflows of cash are unfavourable.
On the data tab all that is required is the change column (B), and the remaining formulas can stay intact.
If you were to track the changes in an income statement, you want to be careful to make sure favourable changes are positive and unfavourable ones negative. For example, if sales are up 100,000, that should be favourable since it has a positive impact on net income. However if expenses are up 100,000 that is unfavourable since it has a negative impact on net income, so although it is technically an increase, the change should be negative. This is where the cumulative change column is helpful because it shows you the running balance, and the ending figure in that column is what you are reconciling to. If that number is not correct then you know somewhere a sign is wrong or an amount is missing.
The remaining columns (D:H) simply have to do with the appearance of the chart. Columns D:E are positive changes, G:H are negative, and F represents the amount that is not visible or blank. The purpose for the blank values is what allows the waterfall chart to create the effect of starting from the last position and just showing the change in the cumulative value.
The project budget template is designed to help track expenses that you do not need to compare against multiple time periods. That being said you could copy the template and create a separate instance for each period you want to cover. This template does not use macros so does not requiring enabling content.
The budget categories can easily be added by just entering a new category in the space below and the formulas will autofill and the chart adjust to contain the new category. To remove a category you can delete the cells and adjust the table by pulling on the corner in the bottom right section of the table (in the overbudget column). This will re-size the data to ensure the chart is not pulling up blank values and making the chart show blank values.
See above for the sample categories, and the chart below summarizes the data visually to show how much of a budget remains, how much has been spent, and how much is overbudget.
