Mortgages

I got e-mail commenting (complaining?) that the XIRR function in Excel ignores leap years. True, but I reckon that the "error" of 1 day in 4 years ain't significant enough to modify any decisions you make which depend upon the value of XIRR. The fella who wrote is in real estate, so I thought I'd consider similar "errors" that are introduced in the name of simplicity. For example, in the U.S., an advertised annual mortgage rate of 6% is taken to mean AnnualRate / 12 per month. That's 6 / 12 = 0.50% per month. Amortized over 30 years, the monthly payments for a $100K mortgage would be $599.55. I think it's the same in France, but not in the U.K. In Canada, for an advertised annual mortgage rate of 12%, the monthly rate would be (1+0.12/2)1/6 - 1 or 0.487% per month. Amortized over 30 years, the monthly payments for a $100K mortgage would be $594.82. See mortgage calculator. Is that the "error" I'm thinking of? No, I'm thinking that monthly payments imply that a year contains 12 months of equal length. Remember? Thirty days hath September ... Okay, suppose we take into consideration the different periods between payments. You take out a $100K mortgage at the beginning of the year. The first payment is made after 31 days, the next after 28 days, etc. If (daily) interest, say r, is charged on the balance, then you'd owe at the end of January (31 days): 100,000 (1+r)31 Then you'd make your payment of $P and the balance would be: 100,000 (1+r)31 - P Another 28 days go by, interest at the daily rate r is applied to this balance, then you'd make your next $P payment and the end-of-February balance would be: 100,000 (1+r)31+28 - P(1+r)28 - P etc.etc. 30 years go by and your final balance is $0. If the "advertised" annual rate is 6%, you'd expect the daily rate to be: r = (1 + 0.06)1/365 - 1   or   0.01597% If you know r, the daily rate, then you can calculate $P your monthly payments, right? Guess what it is? P.S. At the (constant) daily rate r (above), a $100K balance would become: $100,448 after 28 days $100,480 after 30 days $100,496 after 31 days. Did somebuddy say: leap year?