Loan Payment Spreadsheet – If we are thinking about asking for a credit and we want to know what you have to pay each month, in an Excel sheet to be able to calculate, Employ the function = PAYMENT
Syntax: PAY (rate; nper; va; vf; type)
Rate: Interest rate referring to loans / 12 (MONTHLY)
Nper: number of total payments made for loans example 5 years * 12
Va: is the actual value of the total amount of a series of future payments. It is known as principal.
Vf: is the future value or a cash balance that we wish to achieve after the last one.
Type: is the number O (ZERO) If the payments are made in the final phase of the periods, or 1 if they do at the beginning. The value of 0 is used.
Within the financial functions we select the Payment function.
In the Rate box we enter the cell containing the interest rate (or directly interest divided between 12 months (months of one year)
In the box Nper, there we must enter the shell containing the number of payments we want to make, if they were in the years in this box we would multiply the cell * 12.
The last argument The value of the loan of the amount requested.
Example. We used in the Excel sheet the cells D3, D4 and D5.
Cell D5 (annual interest rate)
Cell D6 (Years)
Ceda D3 (Requested capital)
And in cell D6 apply the following formula to quit monthly: = -PAGO (D4 / 12; D5 * 12; D3)
In the following image you can see the example, if you want to open the Excel sheet, where you can calculate a monthly installment loan you can do on the link below it.
When you open the sheet you just have to enter the capital you are requesting, the interest rate and the time in the years and automatically calculate the fee of one pay each month.
The company has an account that the request for a loan to a financial institution may have other commissions or expenses, opening, cancellation or other types of commissions that we must take into account, since it can increase the capital they can pay.