Making charts is one of the most common use of Excel or other spreadsheet software. But do you know a simple trick that can save you lot of time while using excel charting features?
Chart Templates or User Defined Charts
yes, using chart templates can save you a lot of time.
If you use a particular type of chart or formatting all the time, you can save all the steps involved in making the chart by using templates.
Here is a simple tutorial on using chart templates in excel.
1. Prepare your chart
First step is to to prepare a chart that you would like to save to template. The chart can be a formatted version of one of the typical excel charts or a more complex combination chart.
2. Now save the chart as a chart template
In excel 2007 you can do this by selecting the chart and going to design tab in the ribbon and clicking on “Save as template”
For earlier versions of excel, right click on the chart and select chart type and go to “custom types” tab. Select “user-defined” as the chart type and click on the Add button to add the chart to excel chart templates.
3. Use the chart templates
Next time you need to insert a chart, use the templates and save time.
In Excel 2007 use the templates option. In earlier versions, use custom types to find your already save templates.
Bonus tip: Moving Chart Templates from One Computer to Another
If you want to move all your chart templates from one computer to another, just go to My Documents \Application Data\Microsoft\Excel and copy the file XLUSRGAL to the other computer. Make sure you are not overwriting the existing XLUSRGAL file, but just add the sheets from one file to another.
If you are using Excel 2007, the chart templates are stored as *.crtx files. Just locate them and copy to target system. Usually they can be found in \AppData\Roaming\Microsoft\Templates\Charts for Vista and My Documents \Application Data\Microsoft\Templates\Charts for XP.
Free Excel Chart Templates to Get you Started
And here is a huge list of beautiful excel chart templates, around 73 of them. Download and use them free. Get even more in our excel downloads page.
This is part of our Spreadcheats series, a 30 day online excel training series for office goers and spreadsheet users. Join today.
20 Responses to “Mortgage Calculator with Extra Payments – Excel Download”
Kia ora Chandoo, thanks for this wonderful template brother. How can I modify this/ or do you have a similar template for a fortnightly
payment? Thank you
Morena Rayd. You can adjust the multiplier / divider to 26 (I think it was set to 12) to see fortnightly details. Watch the companion video to understand the steps / logic.
It is very simple and easy to use. Is user interface is attractive. It is very helpful and beneficial for calculations.
This is great, thanks a lot!
I think there is an error in the "You save X $ in interest". If i dont put any extra payment, it still says that I will save 19k in interest rate.
I too realized this problem. I will post an update once I fix the error.
Updated on 11-Sep: I fixed the problem with "interest saved" calculation. Turns out there was an internal bug in cumipmt function in Excel that reports incorrect values. Something new!
Please use the link above to re-download the correct file.
Hi Chandoo, do you have a template that will make changes to figures going forward if the interest rate changes part way through the loan? Thank you for your help.
I wanted calculator which can consider floating interest rate, can you please advise how to get that?
I love your tutorial. If I am making weekly acelerated mortgage payments, how to I account for that in the Eff term?
Thank yo so much,
Christine
Hi Chandoo. I want an excel template where I can have my favourite stocks listed for which the prices changes on a dynamic basis every day as per the market. I mean a sheet which is linked to NSE for price changes and other changes like daily low, Daily high, Yesterday closing, last 10 day range, 52 week high, 52 week low, one month high, one month low, one week high and one week low etc
The excel is awesome but I am getting #NAME? error with the field 'Old Interest Paid' and subsequently Original Interest and Money saved. Can you tell me how to fix that. I am trying to check if I should complete my loan by the extra payments or instead invest the same amount elsewhere
#NAME error could mean your Excel doesn't have one of the functions I am using. Can you tell me what version you are using? If I am not mistaken, this file requires Excel 365.
Hi Chandoo, I am also getting the same error. I am using Office 2019. Would you be able to recommend a fix for this?
Regards,
How do you change the dollar to rupees for whole sheet to match indian currency
You can select the values and apply rupee formatting from cell formatting options.
Hey Chandoo,
Thank you for making the excel spreadsheet! It is great, but there another, little known money saving action that you can take to DRAMATICALLY reduce your total interest owed on your mortgage allowing you to pay it off 40% to 60% faster. I am going to share with you for free. It's called recasting your mortgage, which is a form of re-amortizing the interest that you owe on your loan by paying the the bank an early payment of $10,000 or more. The bank will modify your loan to cancel the original interest that you owe, and recalculate the total interest based on the new lower loan loan amount for the remaining term of the loan, instead of the original loan amount. This pays off the most expensive section of interest on your loan which is the next owed interest, instead of cancelling the interest at the end of your loan. Your charts show the interest being canceled on the last payments, however the interest can be canceled on your next upcoming portion of the loan, but keeps the ending date the same. However, the result of recasting the loan is you, pay the loan off 2 or 3 times faster, by paying off the portion of the loan with the highest interest cost. I am wondering if you can build a excel spreadsheet that shows this for me? I will pay you for making it. I am a mortgage loan officer and would like to use it to help my clients see the impact of paying off their loans this way. Thank you for considering this!
HI Chandoo.
Excellent sheet i watched your video and tried to edit sheet to suit my current mortgage however i cant get it to work. I am trying to set it to weekly payments.
I edited the monthly payments to =PMT(E7/52,E6*52,E5)
eff.term =ROUND(NPER($E$7/52,$E$10,$D13),0)
principal term to =PPMT($E$7/52,1,E13,D13)
cb w/o to =IF(C13=1,$E$5,J12)+PPMT($E$7/52,C13,$E$6*52,$E$5)
and interest paid to =IPMT($E$7/52,1,E13,D13) but i cant edit the arrays to increase the range to 1560 from 360, so i just added the numbers in and that worked for the array on the left but the old interest paid array im stuck. And the graph well the graph is useless now and i have no idea how to fix that. no idea at all.
Any help would be great.
Cheers
To change the NUMBER OF ROWS, go to cell C13 and change the formula to
=SEQUENCE(E6*52)
The chart won't auto-adjust. So you need to adjust the chart's source data range to capture all rows.
Hey man, I really appreciate your contribution, this template is awesome, I am sure it will help me to plane my financial life, and also to pay debts earlier and create wealth.
Thank you!!!
how do i edit this to use months instead of years for term? i have a 90 month loan (7.5 years) that i am trying to use with this and it forces me to use 7 or 8 in years.