Buzz
The PMT function in Excel is a financial tool used to calculate the payment amount for a loan based on regular payments and a fixed interest rate. PMT is not only beneficial for businesses but also practical for users looking to calculate repayments for a specific loan.
For instance, consider a loan of 100,000,000 VND with a 27% annual interest rate, to be repaid over 5 years. What is the average amount to be paid each year to clear the loan in 5 years? Sounds complicated, right? Mytour will guide you on how to apply PMT to solve this problem.
Function structure and usage
The PMT function has the following structure: =PMT(rate, nper, pv, [fv], [type])
Where the parameters are:
- Rate: Required parameter. Interest rate per period of the loan.
- Nper: Required parameter. Total number of payment periods for the loan.
- Pv: Required parameter. Present value, also known as the principal amount.
- Fv: Optional parameter. Future value or cash balance you want to attain after making the last payment. If fv is omitted, it defaults to 0 (none), meaning the future value of the loan is 0 (fully paid).
- Type: Optional parameter. 0 (none) or 1 indicating the timing of payments due. If Type is omitted, it defaults to 0, representing the end-of-period payments, and 1 represents the beginning-of-period payments.
Note:
- The payment amount returned by the PMT function includes both principal and interest but does not encompass any fees, charges, or taxes sometimes associated with the loan.
- The interest rate parameter rate and the payment period parameter nper must be consistent in time units. For instance, if you make periodic payments monthly, the interest rate should be converted to a monthly rate by dividing the annual rate by 12 months.
Specific Example
Example 1: For the problem presented earlier, input the following formula into any cell =PMT(27%,5,100000000,0,0); the result obtained is -38,719,579.42. This means that to borrow an amount of 100,000,000 VND, with a 27% annual interest rate, repayable over 5 years, with interest paid at the end, you need to pay 38,719,579.42 VND annually.
The PMT function automatically adjusts currency formatting. If you want to remove the dollar sign and change the format of the result, select the cell to be formatted, press the Ctrl + 1 key combination, and switch to the desired format.
Example 2: You set a goal to regularly deposit money into a bank for 2 years, with a deposit interest rate of 6% per annum. You aim to accumulate 100,000,000 VND after one year. To calculate the monthly deposit needed, use the formula: =PMT(6%/12,2*12,0,100000000). Since you make payments monthly, both the interest rate and total payment periods should be converted to monthly. The monthly result indicates that you need to deposit at least 3,932,061.03 VND into the bank.
Hope this article helps you apply the PMT function in Excel. Wishing you success!
