<?php
$date = "2016/6";
$rate = "2.90";
$summer = 7;
$winter = 1;
$bonus = 100000;
$bcount = 30;
$rate = $rate / 100;
$hoge = get1_3($date, $rate, $summer, $winter, $bonus, $bcount);
echo $hoge;
function get1_3($date, $rate, $summer, $winter, $bonus, $bcount) {
// 【1-3-1-1-1】AQ11 「初回利率」(少数12桁)
// = +ROUNDDOWN(★手数料率(年利) * AP10 / 12, 12)
$b_first_rate = getFirstRate($date, $rate, $summer, $winter);
##OK
#echo $b_first_rate . "\r\n";
#exit;
// 【1-3-1-1-2】AP4 「月利」(少数12桁)
// = ROUND(★手数料率(年利) / 12, 12)
$b_month_rate = round($rate / 12, 12);
##OK
#echo $b_month_rate . "\r\n";
#exit;
// 【1-3-1-1-4】AV18
// = +IF(ボーナス月1 = 8, VLOOKUP(★開始月, "1~12", 2, 0), VLOOKUP(★開始月, "5 or 7", 3, 0))
$t_date = explode("/", $date);
$month = $t_date[1];
if(8 == $summer) {
$a_bonusMonthArray = array( 1 => 7, 2 => 7, 3 => 7, 4 => 7, 5 => 7, 6 => 7, 7 => 7, 8 => 5, 9 => 5, 10 => 5, 11 => 5, 12 => 5);
} else {
$a_bonusMonthArray = array( 1 => 5, 2 => 7, 3 => 7, 4 => 7, 5 => 7, 6 => 7, 7 => 7, 8 => 7, 9 => 5, 10 => 5, 11 => 5, 12 => 5);
}
$keyNumber1 = $a_bonusMonthArray[$month];
##OK
#echo $keyNumber1 . "\r\n";
// ???
// 【1-3-1-1-3】AW18
// = +IF(【1-3-1-1-4】 = 5, 7, 5)
if(5 == $keyNumber1) {
$keyNumber2 = 7;
} else {
$keyNumber2 = 5;
}
#OK
#echo $keyNumber2 . "\r\n";
#exit;
//【1-3-1-1】AP22 「賦金率分子」
// = (1 + 【1-3-1-1-1】) * (1 + 【1-3-1-1-2】 * 【1-3-1-1-3】)^((【1-0-1】-1) / 2) * (1 + 【1-3-1-1-2】 * 【1-3-1-1-4】)^((【1-0-1】- 1) / 2) * ((1 + 【1-3-1-1-2】 * 【1-3-1-1-3】) * (1 + 【1-3-1-1-2】 * 【1-3-1-1-4】) - 1)
$b_inst_numerator = (1 + $b_first_rate) * pow((1 + $b_month_rate * $keyNumber2),(($bcount - 1) / 2)) * pow((1 + $b_month_rate * $keyNumber1),(($bcount- 1) / 2)) * ((1 + $b_month_rate * $keyNumber2) * (1 + $b_month_rate * $keyNumber1) - 1);
//【1-3-1-2】AP23 賦金率分母」
// = (1 + (1 + 【1-3-1-1-2】*【1-3-1-1-4】)) * ((1 +【1-3-1-1-2】*【1-3-1-1-3】)^((【1-0-1】-1)/2) * (1 +【1-3-1-1-2】*【1-3-1-1-4】)^((【1-0-1】- 1) / 2) - 1) + (1 +【1-3-1-1-2】*【1-3-1-1-3】)^((【1-0-1】- 1) / 2) * (1 +【1-3-1-1-2】*【1-3-1-1-4】) ^ ((【1-0-1】- 1) / 2) * ((1 +【1-3-1-1-2】*【1-3-1-1-3】) * (1 + 【1-3-1-1-2】*【1-3-1-1-4】) - 1)
$b_inst_denominator = (1 + (1 + $b_month_rate * $keyNumber1)) * ( pow((1 + $b_month_rate * $keyNumber2),(($bcount - 1) / 2)) * pow((1 + $b_month_rate * $keyNumber1),(($bcount - 1) / 2)) - 1 ) + pow((1 + $b_month_rate * $keyNumber2),(($bcount - 1) / 2)) * pow((1 + $b_month_rate * $keyNumber1),(($bcount - 1) / 2)) * ((1 + $b_month_rate * $keyNumber2) * (1 + $b_month_rate * $keyNumber1) - 1);
##OK
#echo $b_inst_numerator . "\r\n";
echo (1 + (1 + $b_month_rate * $keyNumber1)) . "\r\n";
echo ( pow((1 + $b_month_rate * $keyNumber2),(($bcount - 1) / 2)) * pow((1 + $b_month_rate * $keyNumber1),(($bcount - 1) / 2)) - 1 ) . "\r\n";
echo pow((1 + $b_month_rate * $keyNumber2),(($bcount - 1) / 2)) . "\r\n";
echo pow((1 + $b_month_rate * $keyNumber1),(($bcount - 1) / 2)) . "\r\n";
echo ((1 + $b_month_rate * $keyNumber2) * (1 + $b_month_rate * $keyNumber1) - 1) . "\r\n";
#echo $b_inst_denominator . "\r\n";
#exit;
#2.016916666669
#0.51800693497964
#1.1902438875944
#1.2753746948852
#0.029204409726278
#1.089109317107
#誤差 0.00000000000005
//【1-3-1】BE24
// = +ROUNDDOWN(【1-3-1-1】 / 【1-3-1-2】, 12)
$b_result = round($b_inst_numerator / $b_inst_denominator, 12, PHP_ROUND_HALF_DOWN);
echo $b_result;
exit;
//【1-3】BE25 ③ボーナス不均・奇数
// = +ROUNDUP(★ボーナス加算希望額 / 【1-3-1】,0)
return round($bonus / $b_result, 0, PHP_ROUND_HALF_UP);
}
function getFirstRate($date, $rate, $summer, $winter) {
//【1-2-1-2】AP11 「初回利率」(少数5桁)
// = +ROUNDDOWN(★手数料率(年利) * 【1-2-1-2-1】 / 12, 5)
// 【1-2-1-2-1】AP10 「初回期間」※支払開始後、最初のボーナス月までのヶ月
$t_date = explode("/", $date);
$month = $t_date[1];
$monthCount = 0;
while($monthCount < 12) {
$monthCount++;
$check = $month + $monthCount;
if($check > 12) {
$check = $check - 12;
}
if($check == $summer || $check == $winter) {
$monthCount++;
break;
}
}
return round($rate * $monthCount / 12, 12, PHP_ROUND_HALF_DOWN);
}
- Output for 5.6.0 - 5.6.27, 7.0.0 - 7.0.20, 7.1.0 - 7.1.33, 7.2.0 - 7.2.33, 7.3.0 - 7.3.33, 7.4.0 - 7.4.33, 8.0.0 - 8.0.30, 8.1.0 - 8.1.27, 8.2.0 - 8.2.17, 8.3.0 - 8.3.4
- 2.016916666669
0.51800693497964
1.1902438875944
1.2753746948852
0.029204409726278
0.040902019228
preferences:
161.3 ms | 403 KiB | 215 Q