No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback!
For example, you can use PPMT to get the principal amount of a payment for the first period, the last period, or any period in between. Formulas are the key to getting things done in Excel. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here. Skip to main content. Calculate principal for given period. Generic formula. Related formulas. Fortunately, it is pretty basic.
The interest payment must always be calculated first, and it is simply the per period here monthly interest rate times the remaining principal:. Before we can calculate the interest and principal we must calculate the payment. It turns out that we cannot use the built-in PMT function for the last payment because it will be a different amount.
So, we have to calculate that last payment based on the interest for the last month and the remaining principal. This makes our payment calculation slightly more complicated. In B13 enter the formula:. That simply uses the regular payment amount if the remaining balance is more than the payment. Otherwise, it adds the remaining balance and the interest for the last month. Finally, we want to wrap all of those functions in the Round function, and specify two decimal places as noted above.
So, the final formulas are:. Note that for the principal in D13, I also added a Min function. This makes sure that you never pay more than the remaining principal amount. We now copy those formulas down to row , which will allow us to have up to payments. You can extend it further if you need a longer amortization period. We can now add a column for calculating the remaining balance. Note that we have skipped over column E because we are going to enter the extra payment there.
Now in F13, we calculate the remaining balance by subtracting the principal payment from the previous balance:. Note that I am testing to see if the previous balance is greater than zero to five decimal places. Enter the amount of remaining payments in the Periods row. Click the first blank cell in the Payments row. Press Enter. Did this summary help you? Yes No. Log in Social login does not work in incognito and private browsers. Please log in with your username or email to continue.
No account yet? Create an account. Edit this Article. We use cookies to make wikiHow great. By using our site, you agree to our cookie policy. Cookie Settings. Learn why people trust wikiHow. Download Article Explore this Article Steps. Tips and Warnings. Related Articles. Article Summary. Open Microsoft Excel.
0コメント