You know, a lot of people nowadays should simply take loans. There are plenty of load calculators available online, but it is always better whey one can calculate this thing by himself. Or with Excel, which is pretty much the same thing.
So, for this option Excel is proud to present a good combination of the PMT formula and the “Goal Seek” tool, available in the Ribbon under Data> What if analysis.
I 
have managed to create the following simple example:
So, what the example does? You simply should edit the values in cells B1, B2 and B3 with correspondingly the annual interest rate, the number of monthly payments and the value of the loan. Once you do this, in cell B5 you obtain a value of the amount you have to pay per month for the loan. In our case it is 100. This value is generated through the PMT() formula of Excel.
Furthermore, you get information about the total money paid & the cost of the loan.
What this model does? Until now it is only a simple calculator of loan sum. However, in order to make it more fascinating we have added the Goal Seek function through VBA coding in the model. Thus, if you want to check how much money can you take from a bank, by paying 50 EUR per month for 36 months with 10% interest rate, you should write “50” in cell B12, and change 12 with 36 in cell B2. Once you are ready you should press the big grey button “Goal Seeker”. Thus, you receive the following picture:
You see clearly that you may take 1549.56 EUR from the bank with these conditions. You will return 1800 and the cost of the loan would be about 250 EUR. This calculator, however does not include any other bank fees or bank taxes.
How do we do it? With a small VBA code, looking like this:
|
1 2 3 4 5 6 7 8 9 |
Option Explicit Sub GoalSeeker() Dim Amount As Double Amount = Range("B12").Value * -1 Range("B5").GoalSeek Goal:=Amount, ChangingCell:=Range("B3") End Sub |
If you want to use this Loan Payment Calculator, feel free to download if from here! 🙂
Enjoy it!