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:
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 Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
