假如借贷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里要锁住。