How to calculate mortgage apr
Things You'll Need
Enter the number of periods you will be borrowing the money in cell A1 of your Excel spreadsheet. For this example, enter 360, which will be the number of periods in a house note. In this instance, 360 periods is 360 months (30 years * 12 months/year). Excel's abbreviation for this part of the formula is nper (number of periods).
Enter the amount that will be paid in a month. The house note costs $1,600/month. Enter "-$1,600" in cell A2. This is the amount of the monthly note. Each month removes $1,600 from the payment owed. Excel's abbreviation for this is pmt (payment).
Enter the amount owed in cell A3. The amount owed in this example is $250,000, which is the original value of the loan amount. Enter $250,000 without the dollar sign and the comma. Excel's abbreviation
for this part of the formula is pv (present value). Present value is the total amount that a series of future payments is worth now. It is the amount of money that is being financed.
Type "0" in cell A4. This is because at the end of the loan the desired balance is zero. The goal is to pay off the home loan. For Excel, this part of the formula is fv (future value). The future value is the balance that is desired after the payments are made.
Type "=Rate(A1,A2,A3,A4)*12" in cell A5. The resulting rate is 7%. This means that the annual percentage rate (APR) is 7% for a $250,000 house for which the payment is $1,600/month. The formula is multiplied by 12 in order to get the Annual Percentage Rate. If 12 were excluded, the rate returned would be the monthly APR.Source: ehow.com