×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

@

用excel计算Amortization Schedule

假如借贷10万,年利息为6%,25年还清。半年利息为3%,semi-compound, 所以有效年利息为 (1+3%)^2-1=6.09%,月利息为 (1+6.09%)^(1/12)-1 =0.4938%

用Excel PMT公式很容易算出Amortization Schedule。

Monthly payment =PMT(monthly rate, number of payment, total mortgage amount)
Each Monthly interest payment = IPMT (monthly rate, period number, number of payment, total mortgage amount)
Each Monthly Principle payment= monthly payment - Each Monthly interest payment
Each monthly Loan balance = loan balance in previous month -principle balance

图片中,注意绿色cell要锁住$$。我可以把模板发给大家。

例子中,number of payment =25 year*12=300. monthly interest rate = 0.493862%, 最初贷款额度=10万。这3个数字在excel里要锁住。

Report

Replies, comments and Discussions:

  • 枫下家园 / 钱财税务 / 用excel计算Amortization Schedule

    假如借贷10万,年利息为6%,25年还清。半年利息为3%,semi-compound, 所以有效年利息为 (1+3%)^2-1=6.09%,月利息为 (1+6.09%)^(1/12)-1 =0.4938%

    用Excel PMT公式很容易算出Amortization Schedule。

    Monthly payment =PMT(monthly rate, number of payment, total mortgage amount)
    Each Monthly interest payment = IPMT (monthly rate, period number, number of payment, total mortgage amount)
    Each Monthly Principle payment= monthly payment - Each Monthly interest payment
    Each monthly Loan balance = loan balance in previous month -principle balance

    图片中,注意绿色cell要锁住$$。我可以把模板发给大家。

    例子中,number of payment =25 year*12=300. monthly interest rate = 0.493862%, 最初贷款额度=10万。这3个数字在excel里要锁住。

    • 谢谢信息!请问可以分享一下excel文件吗?
      • 刚用financial calculator给你验证了一下,结果正确!
      • PM 你的email,我发给你.