MS Excel has special financial functions. To choose a function, it is necessary to click on "Insert" and "Functions". To calculate the loan payment, you can use the function PMT. It can be applied, if payments are periodic.
In the given example the function PMT is entered into the cell F2: =PMT (A2; B2; C2; D2; E2). A bank has given the 12 month loan at an interest rate of 29%. 29% / 12 months = 2.42%. For payment calculation it is necessary to enter the data: rate - loan percent for one period. nper - quantity of the periods. pv - the sum. fv and type - optional. Calculations show that the loan payment is $1227.97 for a month. Now it is possible to calculate the internal rate of profitableness of bank for this loan. For this purpose you can use the function "IRR". In this case you can pay $1230 a month and reduce the payment sum in last month.
The formula is entered in the cell H3: IRR(C2:C14). The annual internal
rate of profitableness of a bank is: 1,0242. To erect in degree of 12 months = 1,33 = 33%.
You can calculate a net present value of investment using the function NPV in cell G2:
=NPV (E1; C3:C14) + ?2 =-2,12. A percent for a month (29 / 12) is specified in the cell
E1. Calculations show that this investment is a little unprofitable for bank as expenses
exceed incomes on $2,12, though arithmetically it is not so.
A PMT function allows to calculate the total loan payment for one period (month, year). This payment consists of two parts: the added percent (interest) and the sum going on repayment of the loan (principal). Excel allows to calculate both these sums. You can use the IPMT function to calculate the bank interest. We will consider an example. The bank has given the three year $8000 loan at the interest rate of 10%. It is necessary to calculate the sum of interest for the third year. The formula is entered in the cell G2 : =IPMT(A2; B2; C2; D2). Bank interest is $295.45.
For calculation of the payment going on repayment of the loan (principal), you can use the function PPMT. For example, we want to calculate the principal for third year.
In cell G2 the formula is entered: =PPMT (A2; B2; C2; D2). So the principal is $2924.47. Now we will check up a total payment:
For calculation of payment for the loan the formula is entered into the cell F2: =PMT (A2; B2; C2; D2; E2). We can check the results: the sum of the added percent (interest) + the sum going on repayment of the loan (principal) should be equal to a total payment: 295,45 + 2924,47 = 3216,92. So, the results has coincided.
Now, you may want to receive ready to use NPV & IRR Excel templates for your needs. If you want to download complete set of financial Excel templates (NPV, IRR, EVA, cash flows, assets, and much more), please visit Financial Management 2008 Excel templates page.