Interest only Loan Calculator Excel

Calculator for loans only

Released - so you can do a little more customizing. Released copy of our beloved loan redemption form marketed under a merchant licence that allows you to use this computer in your consultancy and consultancy activity (resale or redistribution is not permitted). Provides information field for company/customer information and corporate logotype.

Basic interest calculator, released and sold under business licence, gives you the right to use this calculator with your customers (resale or sale is not allowed). Provides information field for company/customer information and corporate logotype.

Write-off loans with Microsoft Excel

Learn how to use Microsoft Excel and other spread sheets to generate a redemption plan for a fixed-rate loan (the next section shows how to deal with additional capital repayments and also contains a table with the same example data). To this end, spread sheets have many benefits over computers, which include versatility, usability, and the ability to format.

A typical, but not always, fully amortising loan is one that requires the same amount of repayment (annuity) throughout the term of the loan. After the last repayment, the loan net amount is fully repaid. Every repayment in this kind of loan shall consist of interest and redemption instalments. There is the existence of the capital repayment, which gradually decreases the credit balances, finally to $0. If additional capital repayments are made, then the residual credit balances will decrease faster than the initially expected credit agreement.

A repayment plan is a spreadsheet containing each loan instalment and a break-down of the amount of interest and capital. Usually, the remainder is also displayed after each transaction. Let's begin by discussing the fundamentals with an example loan (if you already know the fundamentals, you can go directly to the creation of an amortisation schedule):

Loan conditions provide for an original capital amount (the amount borrowed) of USD 200,000 and an annual percentage rate of charge of 6.75%. They are paid every month. How much is the montly fee? What will be interest on the first installment and how much will capital be? Calculating the amount of the month's total amount is our first priorities.

The easiest way to achieve this is to use Excel's PMT feature. Please be aware that since we make monetary contributions, we have to adapt the number of cycles (NPer) and the interest rates (rate) to reflect this. Therefore, we must now divide this disbursement into its interest and main elements.

You can do this with a few easy equations (we'll use some built-in features right away): By using these formula we can see that the interest rate would be the first payment: and the capital one:: Notice that the amount of interest and capital is the amount of the overall payment:

This is the case for each individual disbursement over the term of the loan. Nevertheless, as the instalments will be made, the remaining amount will decrease. This in turn means that the interest paid for each subsequent payout is lower and the capital paid is higher (since the overall amount of the payout is constant).

We have now seen how the capital and interest rate component of each transaction is computed. You can, however, use a few built-in features to perform the mathematics for you. Those features also facilitate the calculation of capital and/or interest on any given transaction. We will use the IPMT (interest payment) and PPMT (capital payment) function from the Finance function.

This function calculates the amount of interest or capital payments made on a particular amount. So if we use our information from above, we can compute the interest amount of the first deposit with: and we get 1,125 dollars. Amount of capital in the first installment is $172.20. These responses are exactly the same as the ones we worked out before.

Please be aware that in both features we have specified that Per (the pay period) for the first payout is 1. We' d give two for the second payday, and so on. There is no built-in Excel feature to determine the amount left over after a payout, but we can do it simply enough with a single form.

Just take the opening account minus the capital deposited on the first payout and you will see that the remainder after one payout is $199,827. 80: As mentioned above, an amortisation plan is a simple list of each and every single transaction and a break-down of interest, capital and outstanding amount.

This loan would have an amortisation chart for the first six month like this: Well, in A column we want a set of numbers from 0 to 360 (the max number of numbers we will allow). Begin with the initial principle in E9 with the formula: =B2.

This links it to the main account as specified in the entrance area. Choose B10 and type in the formula: and you will see that the total amount paid per month is $1,297.20, as shown above. For C10 we will compute the interest part of the first payout using the formula:

Much of the amount of the fee can be charged in D10 with: Eventually we compute the residual E10 using the formula: Now you can go to the entry area (B2:B5) and make changes to the loan conditions. I' ve spiced it up a little, just for laughs and for some functions, by making some IF instructions, conditioned formattings, and a graph showing the amount left over over the years.

Most of the times the formula we have submitted above for the payout, the interest, the capital and the remainder will work. After the last payout, for example, the remainder can be shown as 0, but Excel might think it's really something like 0.0000000000000015. So it is useful to adapt the results of our formula as soon as the remainder is small enough to actually be 0. If the remainder is small enough, I will tell the formula to handle it as 0 using the rounding feature to round the remainder to 5 digits to the right of the number.

Again, the only amendment is that the formulae first verify whether the remainder is substantially zero. lf not, they charge normally. Remember that we have arranged this table so that it can process a 30 year month payment at most. If the credit period were shorter (e.g. 15 years), what would they do?

Now, you would end up with a pile of lines with zeroes in them after the loan is disbursed. This can be fixed with the Conditional Formatting feature integrated in the latest version of Excel. It would also be great if we could emphasize the last one.

Set the text colour for all rows to blank after the last payout. It is possible to find out whether a row is after the last payout by checking the payout number (in col. A) against the overall number of payouts (B3*B5). I' m using the "Formula is" checkbox, so choose it from the dropdown box and then input the formula: =$A10>($B$3*$B$5) and input it exactly.

And the second conditioned form just highlights the very last pay. If this is the case, we will apply almost the same logical approach, except that we test to see if we are at the last payout and not afterwards. Calculate: =$A10=($B$3*$B$5). Browse down the spreadsheet and you should see an underscore after the 180 is paid and that all underlying empty cell numbers are empty.

My last improvement is to make a graph that shows the amount of money left that decreases over the years. There is also a how-to that shows how to build an amortisation plan with additional capital outlay.