How to Calculate Monthly Payments in Excel

How to Calculate Monthly Payments in Excel

By an eHow Contributor

Calculating monthly payments is one of the most powerful functions in Excel. The payments calculator allows you to determine what your monthly payment will be for a given loan and allows you to compare monthly payments for different interest rates and repayment periods. Additionally, the payment functionality allows you to build a payment schedule and lets you easily calculate how much interest you can save by paying off a loan early. Using Excel to calculate monthly payments is relatively easy once you have the necessary information about your loans.

Difficulty: Moderately Easy

Instructions

Things You'll Need

Master promissory note from your lender Microsoft Excel

1. o 1

Collect the relevant information from your master promissory note, including the total amount of principal you owe, the interest rate on the loan and the number of years you have to repay the loan. If you did not save the original master promissory note you signed, you will have to request a copy from your lender.

o 2

Use the payment formula in Excel to calculate your monthly payment. The payment formula is as follows: =PMT(rate,nper,pv) where "rate" is the interest rate on the loan, "nper" is the total number of payments you will make and "pv" is the amount of principal that you owe. For example, suppose you have a $25,000 loan at a 6 percent annual interest rate that requires you to make monthly payments for 10 years. To calculate the monthly payment in Excel, enter =PMT(.5%,120,25000). Note that .5% = 6%/12 since 6 percent is the annual interest rate and you are making monthly payments. Also, you need to enter 120 for "nper" since you will be making 12 monthly payments each year for 10 years. This calculation tells you that your monthly payment equals $278.

o 3

You can decompose the monthly payment into principal vs. interest. Multiply the monthly interest rate (in this case .5 percent) by the amount of principal outstanding to determine the amount of the payment used to pay interest. Subtract the amount of interest from the monthly payment to determine the amount of the payment used to pay principal. In this example, the amount of the payment used to pay interest equals 0.5% x $25,000 = $125 and the amount of the payment used to pay principal equals $278 - $125 = $153. The amount of principal outstanding after the first payment equals $25,000 - $153 = $24,847.

Tips & Warnings

You can use the method outlined in Step 3 to calculate a repayment schedule for your loan. You will note that early on in the repayment period, a large portion of your monthly payment will be used to pay interest rather than principal. As you begin to pay the principal down, less of the payment will be used for interest, and you will pay the principal down faster.

Be wary of any prepayment penalties, which some lenders impose if you decide to pay off a loan early. If the penalties are large enough, they may offset any interest savings you will realize from paying the loan off early. Prepayment penalties will be spelled out clearly in your promissory note, and you should try and negotiate out of prepayment penalties when signing up for the loan.

Resources

Read this Article in Spanish Formulas to Calculate Loan Payments in Microsoft Excel

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download