Need Formula To Calculate Payment And Interest

You need TWO columns for this (plus your data columns)

You'll use the PMT and IPMT functions. Excel HELP has info for each.

example:

In column A enter the total amount of the loan
In column D enter the interest rate divided by the number of payments each year
(e.g. 7.5% annual with 12 payments per year "=.075/12")-no quotes
In column E enter the term of the loan (e.g. 36 [months])
In column B enter this formula =PMT(D1,E1,A1)
In column C enter this formula =IPMT(D1,1,E1,A1)

B1 will show the required monthly payment for your term, C1 will show the interest on this initial payment. note: these will be negative numbers (as payments are!)

If you want to amortize this through the life of the loan:
in A2 enter =A1-ABS(B1-C1) and copy this down as many rows as number of payments
in B2 enter =$B$1 and copy down to end of payments
in C2 copy C1 down to end of payments
repeat for D and E (this numbers need to remain unchaged as they are copied down)


Good Luck