
Using Excel. Among the many functions available in Excel is the payment function (PMT). The following chart shows an example of how this may be used.
Note that 3 assumptions are used. 1) the interest rate, 2) the length of a year and 3) the amount of the loan. These 3 must be fixed in the formula. The formula is as follows: =PMT($C$5%/$C$7,E6,$C$9)*1 PMT is the function. Next is the location of the Interest rate. Note the use of $ to lock the address. This allows copying to multiple locations without retyping. The % sign is also important as it tells Excel what is going on. $C$7 locks in the year length. Obviously this will always be 12. However, as the formula works with months it is necessary to define how many are in a year. The only "relative" figure in this formula is the number of payments. Because of this it is not locked in with the $. The amount of being borrowed is also locked in. Finally, the whole thing is multiplied by –1 to make it a positive number. To make the data of more value 2 additional columns are added. The First Total Payback is just the number of payments times the payment amount. The 2^{nd} Cost of Loan, is the total payback minus the original loan amount. For a free copy of this spreadsheet email me at SBTBILL@AOL.COM
