Microsoft Excel is a very power tool for any(almost) types of mathematical calculations. Calculating loans is quite easy in Excel. Before you start calculating loan in Excel you should know some basic principals about Loans.
Most loans have the following five factors:
- Loan principal: The amount borrowed.
- Interest rate: The rate that a borrower pays to the lender. Usually it expressed as a percent(of the principal) per year.
- Payment period: The period of paying. This most often monthly for individual loans and could be quarter/half/yearly for big commercial loans.
- Duration of the loan: For how long the loan principal is borrowed. Or the count of payment periods.
- Installment: The amount you pay each payment period.
Each of these factors listed here is related to all the others. If you borrow more you have to pay more amount monthly or if the interest rate is high/low you have to pay higher/lower respectively.
Lets make a calculation
Say we have borrowed $5,000 for 24 months with a monthly payment basis in a interest rate of 5% per annum.
for this calculation Excel has function
PMT(rt, nper, pv, [Fv], [type])
- Rate is the interest rate for the loan.
- Nper is the total number of payments for the loan.
- Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
- Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
- Type is the number 0 (zero) or 1 and indicates when payments are due
Lest use this function in excel
Make a sheet like this one show here
and paste this code in the cell “B6”
here B3 is divided by 12 because the interest rate is yearly and we are paying a monthly installment.
that’s it and you get your monthly installment for this loan amount
Here I made another calculation of the amount paid in total in the cell “B7”. This is done by multiplying the Installment(B6) with the Duration of the loan(B5).
Hope you like this…