英文:
How can I raise a value to a negative power in Google Sheets?
问题
我正在尝试计算抵押贷款付款，而不使用PMT函数。我想更深入地了解计算抵押贷款付款背后的数学原理，而不只是要求得到结果。方程的一部分涉及到(1-i)^-360，但我不确定如何将一个值升幂为负数。
我尝试在公式栏中输入"x^-y"，但似乎没有执行我想要的操作，即将x升幂为负y。
英文:
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.
答案1
得分: 2
以下是已翻译的部分：
有几种抵押贷款计算公式的变体。花了一些时间才找到一个带有负指数的。
内置的Google函数是 POW(base, exponent)
抵押贷款付款的公式 = i x PV / 1-(1+i)^-N
例如:
- PV = $500,000
- i=5% 每年 => 5%/12 每月 (0.00416666666666667)
- 年数 = 25 => 300 个月 (25 x 12)
- 计算
- 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
证明：使用内置函数 (PMT)
PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])PMT(0.00416666666666667, 300, 500,000, 0, end)= -2922.9502075399
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论