回複：強，但Excel的這個功能似乎有個嚴重錯誤。

This is a very interest discussion. I think it is due to the difference between Mortgage and APR (annual percentage rate).

1. The most accurate compounding is 'continuous compounding' which used in option valuation, such as Black-Schole Model. The formula is: e^rate.

2. For daily compunding as the mortgage providers clami, it should be
(1+daily rate)^365

3. For monthly compuning will be exactly as you said above, (1+monthly rate)^12

4. However for mortgage payment calculating, it is the MORTGAGE RATE used, not the APR:

PV annuity = Amount x (1/r) x [1 - 1/(1+r)^n]. The annual rate / 12 is used.

That is why mortgage providers give both mortgage rate and APR.

Thus, Excel's PMT and IPMT are correct.