The way these payments are split isn’t the same from month to month, however. The more payments you make, the smaller your interest payments become. As a consequence, the amount that goes towards reducing your loan (the payment on principal) increases every month. If you want to know the proportion of your loan payment that’s going towards paying off your outstanding balance, you can use the PPMT function in Microsoft Excel to help you.
What is the PPMT Function in Excel?
If you want to know exactly how much of your monthly payment is a payment on principal, you can use the PPMT function in Excel. This function will calculate the payment on principle for any payment of your loan from the first to the last. You’ll need to supply it with some key information, however. The syntax of the PPMT function is PPMT(rate, per, nper, pv, [fv], [type]) where:
Rate (required) is the interest rate per period.Per (required) is the payment period you want to focus on, such as month two.Nper (required) is the total number of payments.Pv (required) is the present value, which for a loan is how much you originally borrowed.Fv (optional) is the future value you want to remain after the last payment. Defaults to zero.Type (optional) indicates if payments are due at the start (1) or end (0) of each period. Defaults to zero.
How to Use the PPMT Function in Excel
The simplest way to use the PPMT function in Excel is to enter all the information about your loan into cells in your spreadsheet. You can then refer to these cells in your formula. By labeling each piece of information you enter, you can easily keep track of what each value represents. To use the PPMT function in Excel:
Open your Excel spreadsheet.Enter the interest rate, target payment period, number of payments, and loan amount into your spreadsheet. Select the cell where you want your payment on principal to appear.Type =PPMT( and select the cell containing your interest rate. If this is an annual rate, and your payments are monthly, type /12 to use the monthly interest rate. Type a comma, then click on the cell containing the target payment period. For example, a value of 1 would calculate the payment on principal value for your first payment. A value of 12 would calculate the payment on principal value for your twelfth payment. Type a comma and select the cell containing the total number of payments. If you are making monthly payments, this would be the number of months rather than the number of years of your loan. Enter another comma and select the loan amount.Type a closed bracket to complete the formula.
Press Enter and the formula will calculate the amount of payment on principal for the month you specified. In this example, in the first month, $147.05 of the original loan amount would be paid off, with the remainder of the monthly payment covering the accrued interest. The result is red and in brackets, indicating that this is an outgoing payment.Since our formula refers to specific cells rather than values, to find the payment on principal in month 12, we simply change the value of that cell, and the result automatically recalculates. The payment on principal is increased, as less of the monthly payment is used to cover interest payments.
Keeping Track of Your Finances Using Excel
Learning how to use the PPMT function in Excel allows you to make what would otherwise be complex calculations quickly and easily. Once you know how to use the function, you can work out the exact amount of your principal loan you will repay for any and all months that the loan is outstanding. There are plenty of other useful Excel functions that can save you time and effort. You can use the TRUNC function in Excel to quickly convert decimals into integers. If you’re working with text, you can use the UPPER and LOWER functions to convert the text to upper or lower case, or the PROPER function to convert text into standard capitalization. Once you’ve created a formula, you can then apply that formula to an entire column. Comment
Δ
