![]() Put a value there if the loan at the end has a fair value. Fair value of loan (optional, defaults to 0). Now in the spreadsheet, I made this value dynamic so that it changes each time the net balance changes, hence can accommodate a change in payment and still recalculate the subsequent interests and payments.Ĥ. It won't anymore be strictly 'years' then, but periods until full repayment of loan. If you have monthly payments, you will need to change this to ``20*12` and change the interest rate accordingly as mentioned above. The number of period until the loan is fully repaid. I would advise only changing the interest rate to a monthly interest rate and re-calculate a more appropriate loan period (for monthly installment, change 11% to =0.11/12 and change the period to =20*12)Ģ. Of course, the number of periods will change. If the payment was on a monthly basis, you could change it to Int/12. ![]() Since we're having 11% for 1 year, leave it as such. You can get a copy of the workbook in the picture through this link and toy a bit with it I guess. If the number of periods of payments (years) changes, then this cannot help you unless it is revised completely (have to take into account payments already made and charges accrued already, potential penalties and so on). I think this is what you were looking for. You can of course manually put a value in the column Payment and the interest and subsequent payments will immediately be recalculated. G2: =-PMT($B$3,$B$4-D2+1,E2,0,0) (This is the 'meat' of the application)Īt this stage, you can drag the 4 formulas down till the last year of payment. In the first line, there are specific formulas: ![]() The interest charges for each year and the payment is displayed. In this example, I used a Principal amount of $ 2,000,000, rate of 11% and Period of 20 years with equal installments. You only have to make a few changes and change the number of rows for the number of years of payments: You can use this little customized "application" in excel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |