Have you got an existing Loan or Mortgage, would like to refinance it and are keen to see how the modification may influence your financial situation?
In this case we have just the right Calculator in Excel for you. It will show you exactly what impact amending multiple Loan Parameters will have.
This Calculator requires Microsoft Excel 2007 or 2010 to be installed on your computer.
To download a sample of this calculator: click here
The sample is limited to max. 30 repayments.
Please do some testing and make sure that it will suit your requirements.
Then you can purchase the full version on this link: click here
Variable Parameters That Can Be Changed
As stated earlier, there are many Parameters you can change and instantly see how refinancing will impact your Loan or Mortgage.
These parameters are:
- Loan Amount
- Loan Start Date
- Interest Rate (annual or “p.a.”)
- Interest Compounding Frequency (how often bank adds interest to your loan amount)
- Repayment Frequency (how often you pay the loan)
- Repayment Dates
- Repayment Amounts
- Add Extra Payments
User Manual and Instructions
This Excel Calculator for Loan Modification was developed so that it easy to use. You will still require some basic Excel knowledge. But even without it, these Instructions will make sure that you will succeed.
Our Loan Calculator consists of 3 parts:
- Entry Parameters Section (top left)
- Loan Amortization Schedule (bottom screen-wide)
- Summary Results (top right)
Ok, now let’s go and have a look at how it works. As already mentioned, you should begin with part (1), then carry on to part (2) and finally see the results in part (3).
1. Data Entry Section for Loan Parameters
In this part you will have to enter the Main Loan Parameters, such as:
- Your Loan Amount. In other words, how much you plan to borrow on your new loan, respectively into what Amount your old Loan is going to be modified.
- Repayment Amount. Majority of Loan Calculators out there will require Loan Term as entry data. Based on the Loan Length they calculate what the Repayment Amount is going to be. Our Excel Calculator is slightly different. It will ask you how much you want to pay and then it figures out how long it will take to repay the Loan.
- Repayment Frequency. You have the ability to select daily, weekly, bi-weekly (fortnightly) or monthly Repayment Frequency
- Interest Compounding Frequency. Many Banks announce Interest Rates as yearly percentage. However, they compound the Interest daily or monthly. Doing so they are pushing up the Total Interest Rate. It’s one of those ethically questionable things they do to make their loans look more attractive. Therefore, always ask your bank up-front how they compound the Interest on your Loan.
- Loan Start Date. Start Date will determine your Repayment Calendar (or Loan Amortization Schedule). The good thing about this Calculator is that you can choose whatever Date you like, even retrospectively. Or in the future – so you can generate new or re-create existing Loan Schedule.
- Yearly Interest Rate. Almost always Interest Rates are published as Yearly percentages. Loan Calculator then automatically adjust it to calculate daily, weekly, fortnightly or monthly Interest. The formula to do that is already built-in into our Calculator.
2. Loan Amortization Schedule
You may have heard different terms, like the Loan Amortization Schedule or the Repayment Calendar – they both mean the same thing.
In this section you will get all details about your periodic Payment Amounts. The schedule will also show you detailed break-down. You will know exactly how much Principal and how much Interest you pay in each Repayment Period.
There is one major improvement compared with other Loan Calculators. You will be able to adjust Repayment Amounts and Actual Payment Date for each Repayment Period.
This is to ensure that the Loan Schedule stays current at all times – for example when you miss a Payment or pay late. Our Calculator will then automatically re-calculate the Interest and adjust the Loan Balance.
Another great improvement is that you are able to add any Extra Payment (Additional Principal) in each Repayment Period.
The fields which can be over-written are highlighted in green color. Just one thing to remember – do not change formulas in any non-green fields. If you do then the Loan Calculator may not work correctly.
3. Loan Summary Results
Once you specify all your Loan Parameters in section (1) and complete Adjustments of your modified Loan Schedule (Payment Dates, Repayment Amounts or Extra Payments) in section (2) then proceed to the Summary Results Section.
Here you will get the following information:
- Total Principal Amount (Should be the same as your original Loan Amount.)
- Total Interest Amount (Paid over the whole Loan Term.)
- Loan Term (That is the time it takes to repay the modified Loan in years)
So, and that’s it. Our Excel Calculator designed for Mortgage Refinance (and modifications of other types of Loans as well) is very easy to use, yet offering quite comprehensive functionality.
It is an ideal tool to get the most out of your Loan. You can also use it for an interactive modelling.
Every update of the Loan Parameters is instantly translated into the Amortization Schedule as well as the Final Results.
We are sure that you will enjoy using our enhanced Excel Calculator. If you have any feedback or would like to suggest some additional features then please don’t hesitate to talk to us. You can reach us via our Contact Form.