银行贷款方案设计
方案摘要当前值: 方案1 方案2 方案3可变单元格:$D$2 方案1 方案1 方案1 方案1$D$3 ¥2,000,000.00 ¥2,000,000.00 ¥2,000,000.00 ¥2,000,000.00$D$4 15 15 15 15$D$5 7.50% 7.50% 7.50% 7.50%$D$6 ¥25,000.00 ¥25,000.00 ¥25,000.00 ¥25,000.00$D$7 2008/8/7 2008/8/7 2008/8/7 2008/8/7$G$2 方案2 方案2 方案2 方案2$G$3 ¥2,000,000.00 ¥2,000,000.00 ¥2,000,000.00 ¥2,000,000.00$G$4 20 20 20 20$G$5 8.20% 8.20% 8.20% 8.20%$G$6 ¥25,000.00 ¥25,000.00 ¥25,000.00 ¥25,000.00$G$7 2008/8/7 2008/8/7 2008/8/7 2008/8/7$J$2 方案3 方案3 方案3 方案3$J$3 ¥2,000,000.00 ¥2,000,000.00 ¥2,000,000.00 ¥2,000,000.00$J$4 30 30 30 30$J$5 9.60% 9.60% 9.60% 9.60%$J$6 ¥25,000.00 ¥25,000.00 ¥25,000.00 ¥25,000.00$J$7 2008/8/7 2008/8/7 2008/8/7 2008/8/7结果单元格:$C$8 ¥-18,540.25 ¥-18,540.25 ¥-18,540.25 ¥-18,540.25$F$8 ¥-16,978.60 ¥-16,978.60 ¥-16,978.60 ¥-16,978.60$I$8 ¥-16,963.20 ¥-16,963.20 ¥-16,963.20 ¥-16,963.20注释: “当前值”这一列表示的是在建立方案汇总时,可变单元格的值。每组方案的可变单元格均以灰色底纹突出显示。银行贷款方案设计方案1 方案2贷款金额 ¥2,000,000.00 ¥2,000,000.00贷款年限 15 20贷款年利率 7.50% 8.20%每月现金收入 ¥25,000.00 ¥25,000.00首次还款日期 2008/8/7 2008/8/7月还款额 ¥-18,540.25 ¥-16,978.60年还款额 ¥-226,574.47 ¥-206,745.56每月剩余现金 ¥6,459.75 ¥8,021.40每年剩余现金 ¥77,517.03 ¥96,256.82偿还总金额 ¥-3,337,244.50 ¥-4,074,863.56年利息支付 年本金支付 年利息支付1 ¥-150,000.00 ¥-76,574.47 ¥-164,000.002 ¥-144,256.91 ¥-82,317.56 ¥-160,494.863 ¥-138,083.10 ¥-88,491.37 ¥-156,702.314 ¥-131,446.24 ¥-95,128.23 ¥-152,598.765 ¥-124,311.63 ¥-102,262.84 ¥-148,158.726 ¥-116,641.91 ¥-109,932.56 ¥-143,354.607 ¥-108,396.97 ¥-118,177.50 ¥-138,156.558 ¥-99,533.66 ¥-127,040.81 ¥-132,532.259 ¥-90,005.60 ¥-136,568.87 ¥-126,446.7610 ¥-79,762.93 ¥-146,811.54 ¥-119,862.2511 ¥-68,752.07 ¥-157,822.40 ¥-112,737.8212 ¥-56,915.39 ¥-169,659.09 ¥-105,029.1913 ¥-44,190.96 ¥-182,383.52 ¥-96,688.4514 ¥-30,512.19 ¥-196,062.28 ¥-87,663.7615 ¥-15,807.52 ¥-210,766.95 ¥-77,899.0616 - - ¥-67,333.6517 - - ¥-55,901.8718 - - ¥-43,532.6919 - - ¥-30,149.2320 - - ¥-15,668.3321 - - -22 - - -23 - - -24 - - -25 - - -26 - - -27 - - -28 - - -29 - - -30 - - -方案3¥2,000,000.00309.60%¥25,000.002008/8/7¥-16,963.20¥-205,112.18¥8,036.80¥96,441.61¥-6,106,751.79年本金支付 年利息支付 年本金支付¥-42,745.56 ¥-192,000.00 ¥-13,112.18¥-46,250.69 ¥-190,741.23 ¥-14,370.95¥-50,043.25 ¥-189,361.62 ¥-15,750.56¥-54,146.79 ¥-187,849.57 ¥-17,262.61¥-58,586.83 ¥-186,192.36 ¥-18,919.82¥-63,390.95 ¥-184,376.05 ¥-20,736.12¥-68,589.01 ¥-182,385.39 ¥-22,726.79¥-74,213.31 ¥-180,203.61 ¥-24,908.56¥-80,298.80 ¥-177,812.39 ¥-27,299.79¥-86,883.30 ¥-175,191.61 ¥-29,920.57¥-94,007.73 ¥-172,319.24 ¥-32,792.94¥-101,716.37 ¥-169,171.11 ¥-35,941.06¥-110,057.11 ¥-165,720.77 ¥-39,391.40¥-119,081.79 ¥-161,939.20 ¥-43,172.98¥-128,846.50 ¥-157,794.59 ¥-47,317.59¥-139,411.91 ¥-153,252.10 ¥-51,860.07¥-150,843.69 ¥-148,273.54 ¥-56,838.64¥-163,212.87 ¥-142,817.03 ¥-62,295.15¥-176,596.32 ¥-136,836.69 ¥-68,275.49¥-191,077.22 ¥-130,282.25 ¥-74,829.93- ¥-123,098.57 ¥-82,013.61- ¥-115,225.27 ¥-89,886.91银行贷款方案设计¥-16,978.60¥-206,745.56¥8,021.40¥96,256.82¥-4,074,863.56- ¥-106,596.12 ¥-98,516.05- ¥-97,138.58 ¥-107,973.60- ¥-86,773.12 ¥-118,339.06- ¥-75,412.57 ¥-129,699.61- ¥-62,961.40 ¥-142,150.77- ¥-49,314.93 ¥-155,797.25- ¥-34,358.39 ¥-170,753.78- ¥-17,966.03 ¥-187,146.15贷 款 统 计 分 析 图月 还 款 额 每 月 剩 余 现 金贷 款 统 计 分 析 图月 还 款 额 每 月 剩 余 现 金月本金支付额 方案1 方案2 方案31 ¥-6,040.25 ¥-3,311.93 ¥-963.202 ¥-6,078.00 ¥-3,334.56 ¥-970.913 ¥-6,115.99 ¥-3,357.35 ¥-978.674 ¥-6,154.21 ¥-3,380.29 ¥-986.505 ¥-6,192.67 ¥-3,403.39 ¥-994.396 ¥-6,231.38 ¥-3,426.65 ¥-1,002.357 ¥-6,270.33 ¥-3,450.06 ¥-1,010.378 ¥-6,309.51 ¥-3,473.64 ¥-1,018.459 ¥-6,348.95 ¥-3,497.37 ¥-1,026.6010 ¥-6,388.63 ¥-3,521.27 ¥-1,034.8111 ¥-6,428.56 ¥-3,545.33 ¥-1,043.0912 ¥-6,468.74 ¥-3,569.56 ¥-1,051.43月利息支付额 方案1 方案2 方案31 ¥-12,500.00 ¥-13,666.67 ¥-16,000.002 ¥-12,462.25 ¥-13,644.04 ¥-15,992.293 ¥-12,424.26 ¥-13,621.25 ¥-15,984.534 ¥-12,386.04 ¥-13,598.31 ¥-15,976.705 ¥-12,347.57 ¥-13,575.21 ¥-15,968.816 ¥-12,308.87 ¥-13,551.95 ¥-15,960.857 ¥-12,269.92 ¥-13,528.54 ¥-15,952.838 ¥-12,230.73 ¥-13,504.96 ¥-15,944.759 ¥-12,191.30 ¥-13,481.22 ¥-15,936.6010 ¥-12,151.62 ¥-13,457.33 ¥-15,928.3911 ¥-12,111.69 ¥-13,433.26 ¥-15,920.1112 ¥-12,071.51 ¥-13,409.04 ¥-15,911.77月利息支付额 方案1 方案2 方案31 ¥12,500.00 ¥13,666.67 ¥16,000.002 ¥12,462.25 ¥13,644.04 ¥15,992.293 ¥12,424.26 ¥13,621.25 ¥15,984.534 ¥12,386.04 ¥13,598.31 ¥15,976.705 ¥12,347.57 ¥13,575.21 ¥15,968.816 ¥12,308.87 ¥13,551.95 ¥15,960.857 ¥12,269.92 ¥13,528.54 ¥15,952.838 ¥12,230.73 ¥13,504.96 ¥15,944.759 ¥12,191.30 ¥13,481.22 ¥15,936.6010 ¥12,151.62 ¥13,457.33 ¥15,928.3911 ¥12,111.69 ¥13,433.26 ¥15,920.1112 ¥12,071.51 ¥13,409.04 ¥15,911.77模拟运算示例贷款总额 ¥150,000.00贷款年利率 4.80%还贷年利息 ¥7,200.00还贷年利息¥7,200.00贷款年利率 4.80% ¥7,200.006.00% ¥9,000.006.50% ¥9,750.007.00% ¥10,500.008.00% ¥12,000.00方案12000000150.0752500039667