In the PMT() function above, the interest rate is also divided by12 and the nper (in years) is multiplied by 12. The payments are monthly, so the interest rate is converted to months. The important thing to remember when building an amortization schedule is to keep your periods consistent. Multiply the loan balance by the interest rate divided by 12. ![]() The interest and payment are calculated, so this column simply subtracts them. If I wanted to make it dynamic, I would conditionally format the rows to make the text color the same as the background color if it exceeds the term. You could make a formula for this, but I never do. Period I just filled a series of numbers down to 360. ![]() The Payment cell has this formulaīecause I am using a positive present value in the PMT() function, I negate the whole function to return a positive payment. This post walks you through creating the schedule from scratch. Creating your own amortization schedule requires only a few functions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |