java Java中的Excel PMT函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7827352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel PMT function in Java
提问by Debarati
I want to implement PMT function (from Excel) in Java. The formula of the PMT function is:
我想在 Java 中实现 PMT 函数（来自 Excel）。PMT函数的公式为：
(D7*D9/12)/(1-(1+D9/12)^(-(D11/12)*12))
where:
在哪里：
- D7 = Finance Amount
- D9 = Rate
- D11 = Term
- D7 = 融资金额
- D9 = 比率
- D11 = 期限
For example
例如
D7 = ,00,000,
D9 = 10%,
D11 = 36
Here the output of PMT function i.e. monthly payment will be $3,226.72
这里 PMT 功能的输出，即每月付款将是 $3,226.72
Please anyone help me to calculate this function value in Java.
请任何人帮助我用 Java 计算这个函数值。
采纳答案by Mike
assuming all variable of double type:
假设所有变量都是双精度型：
result=(d7*d9/12)/Math.pow((1-(1+D9/12),(-(d11/12)*12)))
回答by Fredy Bello
You can use Apache POI:
您可以使用 Apache POI：
http://poi.apache.org/apidocs/org/apache/poi/ss/formula/functions/FinanceLib.html
http://poi.apache.org/apidocs/org/apache/poi/ss/formula/functions/FinanceLib.html
It saves my day :D
它拯救了我的一天：D
Usage example:
用法示例：
FinanceLib.pmt(0.00740260861, 180, -984698, 0, false)
Parameters: rate, months, present value, future value, at the beginning of the period(or at the end)
参数：rate, months, present value, future value, at the beginning of the period（或在最后）
回答by Ding
//D7:Finance Amount
//D9 = Rate(the rate here is annual rate)
//D11 = Term
fv=D7 * Math.pow((1 + D9/12), D11);
PMT＝(fv * D9/12) / (pow((1 + D9/12), D11) - 1);
回答by Shravan Kumar
This is an Excel PMT equivalent in JAVA:
这是 JAVA 中的 Excel PMT 等效项：
public BigDecimal calcEMI(BigDecimal P, BigDecimal N, BigDecimal ROI) {
if(BigDecimalUtil.anyOneZeroOrNull(P,N,ROI)){
SCBSUtils.throwSmartCBSException("Invalid data for calculating EMI %s, %s %s", P,N,ROI);
}
MathContext mc = MathContext.DECIMAL128;
//Excel equivalent formula == P*R*POWER(1+R,N)/(POWER(1+R,N)-1)
BigDecimal R = ROI.divide(new BigDecimal(1200),mc);
BigDecimal nemi1 = P.multiply(R,mc);
BigDecimal npower1 = (BigDecimal.ONE.add(R)).pow(N.intValue(),mc);
BigDecimal dpower1 = (BigDecimal.ONE.add(R)).pow(N.intValue(),mc);
BigDecimal denominator = dpower1.subtract(BigDecimal.ONE);
BigDecimal numerator = nemi1.multiply(npower1);
BigDecimal emi = numerator.divide(denominator,0,RoundingMode.UP);
return emi;
}
回答by MariuszS
Something like this:
像这样的东西：
public static void main(String[] args) {
MathContext MATH_CONTEXT = new MathContext(18, RoundingMode.HALF_UP);
BigDecimal pmt = pmt(new BigDecimal("-100000", MATH_CONTEXT),
new BigDecimal("10", MATH_CONTEXT),
new BigInteger("36"));
}
public static BigDecimal pmt(BigDecimal D7, BigDecimal D9, BigInteger D11) {
BigDecimal rate = D9
.divide(new BigDecimal("100"), MATH_CONTEXT)
.divide(new BigDecimal("12"), MATH_CONTEXT);
return rate.negate(MATH_CONTEXT)
.multiply(D7.multiply(pow(BigDecimal.ONE.add(rate), D11), MATH_CONTEXT))
.divide(pow(BigDecimal.ONE.add(rate), D11).subtract(BigDecimal.ONE), MATH_CONTEXT);
}
private static BigDecimal pow(BigDecimal x, BigInteger n) {
return x.pow(n.intValue(), MATH_CONTEXT);
}
回答by stakahop
Excel PMT function calculates annuity using Proportional method. A lot of people now use Conformal method instead, where annuity is a little lower.
Excel PMT 函数使用比例法计算年金。现在很多人改用保形方法，年金稍微低一点。
PMT implementation in JAVA:
JAVA中的PMT实现：
private static double calculateAnnuity(double creditAmmount, double interestRate, double numberOfYears, int calculationPeriod) {
double annuitiesPerYear = 12.0 / (numberOfYears * 12.0) * (numberOfYears * 12.0) / calculationPeriod;
double period = 1 / annuitiesPerYear;
double interest = Math.pow(1 + (interestRate / 100), period) - 1;
double decursiveFactor = 1 + interest;
double annuityCalc = (Math.pow(decursiveFactor, numberOfYears * 12) * (decursiveFactor - 1)) / (Math.pow(decursiveFactor, numberOfYears * 12) - 1);
return Math.round((creditAmmount * annuityCalc) * 100.0) / 100.0;
}
// Test - credit ammount: 10000, interest rate 8.0 %, tenor: 60 months, .
public static void main(String [] args){
System.out.println(calculateAnnuity(10000, 8.0, 60/12.0, 1));
}
回答by HRHeeb
None of the other answers posted so far really seem to match Excel's results.
到目前为止发布的其他答案似乎都与 Excel 的结果不符。
This works for me:
这对我有用：
if (D9 < 1E-6) {
return (D7 / D11);
}
return (D7*D9) / (1.0 - Math.pow(1 + D9, -D11));
回答by Archimedes Trajano
This is my attempt, primarily tried to keep it to have the same parameters as Excel. I took it off the code from Apache POI and switched it to use BigDecimals.
这是我的尝试，主要是试图让它与 Excel 具有相同的参数。我把它从 Apache POI 的代码中取出并切换到使用BigDecimals。
/**
* PMT function ported from Excel to Java to use BigDecimals.
* @param interestRate interest rate for the loan.
* @param numberOfPayments is the total number of payments for the loan.
* @param principal is the present value; also known as the principal.
* @param futureValue It is the future value, or the balance that you want to have left after the last payment. If fv is omitted, the fv is assumed to be zero.
* @param paymentsDueAtBeginningOfPeriod payments are due at the beginning of the period.
* @return payment
* @see <a href="https://apache.googlesource.com/poi/+/4d81d34d5d566cb22f21999e653a5829cc678ed5/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java#143">FincanceLib</a>
*/
public static BigDecimal pmt(BigDecimal interestRate,
int numberOfPayments,
BigDecimal principal,
BigDecimal futureValue,
boolean paymentsDueAtBeginningOfPeriod) {
final BigDecimal n = new BigDecimal(numberOfPayments);
if (BigDecimal.ZERO.equals(interestRate)) {
return (futureValue.add(principal)).divide(n, MathContext.DECIMAL128).negate();
} else {
final BigDecimal r1 = interestRate.add(BigDecimal.ONE);
final BigDecimal pow = r1.pow(numberOfPayments);
final BigDecimal divisor;
if (paymentsDueAtBeginningOfPeriod) {
divisor = r1.multiply(BigDecimal.ONE.subtract(pow));
} else {
divisor = BigDecimal.ONE.subtract(pow);
}
return (principal.multiply(pow).add(futureValue)).multiply(interestRate).divide(divisor, MathContext.DECIMAL128);
}
}
You would likely get issues because of the decimals so round accordingly.
由于小数点相应地四舍五入，您可能会遇到问题。
@Test
public void testPMT() {
assertEquals(
new BigDecimal("-3756.00"),
pmt(
new BigDecimal("0.0075000"),
36,
new BigDecimal("119000.00"),
BigDecimal.ZERO,
true
).setScale(2, RoundingMode.HALF_UP)
);
}
回答by Kamil Witkowski
Apache POIhas method for this but it operates on doublewhich is not the best for the use case when you use money.
Apache POIdouble有用于此的方法，但是当您使用money.
public static BigDecimal pmt(BigDecimal rate, Integer months, BigDecimal presentValue, boolean t) {
BigDecimal result = BigDecimal.ZERO;
if (rate.compareTo(BigDecimal.ZERO) == 0) {
result = new BigDecimal("-1.0").multiply(presentValue).divide(new BigDecimal(months), RoundingMode.HALF_UP);
} else {
BigDecimal r1 = rate.add(BigDecimal.ONE);
BigDecimal opt = t ? r1 : BigDecimal.ONE;
result = presentValue.multiply(r1.pow(months)).multiply(rate)
.divide( opt.multiply(BigDecimal.ONE.subtract(r1.pow(months))), RoundingMode.HALF_UP);
}
return result;
}
Call it with for example:
例如调用它：
BigDecimal testPMT = pmt(new BigDecimal("0.002675"), 25, new BigDecial("300000"), false);
Result: -12421.758816(...)- the same as in excel with =PMT((0.002675),25,300000)
结果：-12421.758816(...)- 与在 excel 中相同=PMT((0.002675),25,300000)
Now you can round result by your needs.
现在您可以根据需要对结果进行四舍五入。
For example like that:
例如像这样：
BigDecimal roundedPMT = testPMT.setScale(2, RoundingMode.HALF_UP);
