CosmeticsLydia

Payless 4           Plumbing

 

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.

Loan Payments

Interest Rate

8.00

# of Payments

Payment

Total Payback

Cost of Loan

12

$869.88

$10,438.61

$438.61

year

12

18

$591.40

$10,645.25

$645.25

24

$452.27

$10,854.55

$854.55

Amount of Loan

10000

30

$368.88

$11,066.50

$1,066.50

36

$313.36

$11,281.09

$1,281.09

42

$273.77

$11,498.33

$1,498.33

48

$244.13

$11,718.20

$1,718.20

54

$221.12

$11,940.71

$1,940.71

60

$202.76

$12,165.84

$2,165.84

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 2nd Cost of Loan, is the total payback minus the original loan amount.

For a free copy of this spreadsheet e-mail me at SBTBILL@AOL.COM

 

Hit Counter