Excel Tutorials | Loans and Credits | #ExcelTutorials

When planning an investment, one of the opportunities companies often resort to is a credit, which can be more profitable than self-finance in some situations . MS Excel can often be applied in such cases of investment valuation.

Excel Tutorials | Loans and credits with MS Excel 

Having to deal with loans and credits, we need to calculate the interest in the first place. For that we use the principal, i.e. the capital disposed at the beginning of the period. In order to find the interest which we will receive or pay (depending on whether we are lenders or borrowers) in the next period, we multiply the principal by the interest rate. The sum of all periods’ interests accounts for the cost of the capital for the contractor.

Amortisation in MS Excel: Excel PPMT Function

Based on our amortisation schedule, we divide the contracted sum into several instalments which are going to be paid in each period. When we use the so-called constant amortisation, the number of instalments is equal to the number of payment periods. At the end of the period, we cut the amortisation sum from the initial capital. The new amount will be the initial capital for the next period. When the loan is payed off, the amount of the capital to be payed is naturally equal to 0.

If the amortisation is not constant, we can use the Excel PPMT function to calculate the amount of principal payment in each period. The Excel PPMT (PRINCPER/KAPZ) function – principal payment – gives us an estimate of the part of the investment principal that is to be payed for a given time period under condition of periodic and constant payments and a constant interest rate.

Calculating the Interest Payment in MS Excel: Excel IPMT Function

The Excel IPMT (INTPER/ZINSZ) function – interest payment – helps us to calculate the interest payment for an investment during the given period of time based on periodic and constant payments as well as a constant interest rate.

Calculating the Constant and Periodic Payment in MS Excel: Excel PMT Function

Together, the amortization plus the interest produce the annuity or monthuality. That is the sum of money which is paid every period (yearly or monthly). In order to calculate the annuity or monthuality in MS Excel, i.e. the constant and periodic payment which is necessary to carry out in order to pay for an investment, we can use the Excel PMT function (an Excel financial formula showing a monthly or yearly interest payment). Provided that we have a constant interest rate, the Excel PMT function helps us to figure out the sum of money to be paid periodically once a loan, morgage or any other type of investment has been contracted.

PMT	PV	RATE	NPER
VPM	VA	TAUX	NPM
RMZ	BW	ZINS	ZZR