How can I raise a value to a negative power in Google Sheets?

Question

I'm trying to calculate a mortgage payment without using the PMT function. I want to get better acquainted with the math behind calculating a mortgage payment, not just have the product produced for me. Part of the equation involves (1-i)^-360 but I'm not sure how to raise a value to a negative power.

I've tried typing "x^-y" in the formula bar, but it doesn't appear to be performing the operation I'm wanting it to, viz. raising x to the negative y.

Answer 1

There are several variants on the mortgage calculation formula. It took me a while to find one with a negative exponent.

The built-in Google function is POW(base, exponent)

Formula for a Mortgage loan Payment = i x PV / 1-(1+i)^-N

Say:

• PV = $500,000
• i=5% p.a. => 5%/12 per month (0.00416666666666667)
• number of years = 25 => 300 months (25 x 12)
• Calculation
  • i x PV = 0.00416666666666667 x 500000 = 2083.33333333333
  • (1+i) = 1-0.00416666666666667 = 1.00416666666667
  • (1+i) ^-N = pow(1.00416666666667,-300) = 0.287249803996227
  • 1-(1+i)^-N = 1-0.287249803996227 = 0.712750196003773
  • i x PV/1-(1+i)^-N = 2083.33333333333/0.712750196003773 = 2922.9502075399

PROOF: Using the Built-in function (PMT)

• PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])
• PMT(0.00416666666666667, 300, 500,000, 0, end) = -2922.9502075399