Learn how to summarize data in a workbook by using worksheet links to create a summary worksheet.Use the PMT function to calculate monthly lease payments for an automobile.Use the PMT function to calculate monthly mortgage payments on a house.Understand the fundamentals of loans and leases.Repeat this step for each payment period until the loan is paid off. In this example, row eight would read $283.87 in A8, $134.61 in B8, $149.26 in C8 and $24,702.29 in D8.Ĭopy and paste row eight into row nine to calculate the values for the third payment period. In cell B8, enter "=ROUND(D7*B$2/1200,2)" and in cell D8 enter "=D7-C8" to calculate the values for the second payment period. Continuing the example, Excel would display $24,851.55.Ĭopy and paste the values from A7 and C7 to A8 and C8. In this example, Excel would display $148.45.Įnter "=B1-C7" in cell D7 to calculate the new balance of the loan.
In this example, Excel would display $135.42.Įnter "=A7-B7" in cell C7 to calculate the principal portion of the payment. The "$" makes sure that when the cell is copied and pasted for future payment periods in the following steps, Excel will continue to use the amount in cell B4 as the monthly payment.Įnter "=ROUND(B1*B$2/1200,2)" in cell B7 to calculate the interest portion of the first month's payment. In cell B4, enter the formula "=-PMT(B2/1200,B3*12,B1)" to have Excel automatically calculate the monthly payment.įor example, if you had a $25,000 loan at 6.5 percent annual interest for 10 years, the monthly payment would be $283.87.Įnter "Payment" in cell A6, "Interest Portion" in cell B6, "Principal Portion" in cell C6 and "New Balance" in cell D6.Įnter "=$B$4" in cell A7 to have Excel automatically transfer the monthly payment calculated in cell B4. Enter the corresponding values in cells B1 through B3.
0 kommentar(er)
