![]() Click on the option use formula to determine which cells to format and enter the following formula: Select the entire table range from E3 to 元62, open up the conditional formatting dialogue from the Home ribbon and select New Rule. ![]() The conditional formatting will mean that any cell that has a 0 in it will be “hidden” and the hidden cells will change automatically if we change our extra repayment amount. Now we can leave our amortisation table as is and it is just fine, or we can use some conditional formatting to hide any 0 amounts and make it look pretty. Everything after that has been changed from negative numbers to zeros. Have a look towards the bottom of the spreadsheet and you will notice that with the extra repayment of $80 per month, we will have paid off our loan 30 months early (period 331). You will notice that for these two formulae, we have used the dollar signs for absolute references to lock in the numbers from our data table for when we fill down. So, if the balance at the beginning of the month is less than the monthly payment amount (B6) then make $0 extra repayment, otherwise, use the extra repayment amount as we set up in B11. Always check with a professional financial advisor before making financial decisions. You should double-check your results with your bank or professional financial advisor. It does not take into account bank fees.ĭisclaimer: This tutorial is for informational purposes only and does not constitute as professional advice. I’ll start with the same information from the PMT exercise, so if you haven’t done principal and interest calculations, do those first.Īll results are based on the assumption that the interest rate remains fixed throughout the entire loan term and that interest is calculated monthly and payments made monthly. Create Your Own Amortization Table in Excel Of course, making loan calculations can be done easily using free online loan calculators, but that’s not as fun as doing it yourself in Excel and this way you can link your information into your budget to run some dynamic financial scenarios. Photo by rawpixel on Unsplash.Ĭheck out the PMT tutorial before starting this one to create the initial data table.
0 Comments
Leave a Reply. |