Home loan calculation formula (algorithm)?

Question

How a bank calculate home loan's payments?

For example,

$1,000,000 at 5.00% over a 25 year period.
Monthly payment: $5,845.90


              Current Payment                                 To Date
Payment  --------------------------        ----------------------------------------------
Number   Interest         Principal        Interest Paid    Principal Paid    Balance
1        $4,166.67        $1,679.23        $4,166.67        $1,679.23         $998,320.77
2        $4,159.67        $1,686.23        $8,326.34        $3,365.46         $996,634.54
3        $4,152.64        $1,693.26        $12,478.98       $5,058.72         $994,941.28
4        $4,145.59        $1,700.31        $16,624.57       $6,759.03         $993,240.97
5        $4,138.50        $1,707.40        $20,763.07       $8,466.43         $991,533.57
6        $4,131.39        $1,714.51        $24,894.46       $10,180.94        $989,819.06
7        $4,124.25        $1,721.65        $29,018.71       $11,902.59        $988,097.41
8        $4,117.07        $1,728.83        $33,135.78       $13,631.42        $986,368.58
9        $4,109.87        $1,736.03        $37,245.65       $15,367.45        $984,632.55
10       $4,102.64        $1,743.26        $41,348.29       $17,110.71        $982,889.29

I'm trying to do same calculations in Excel, but I get another numbers...

Answer 1

the magic words are amortization schedule.

Answer 2

The difference you see in Excel is probably to do with the way the compound interest is calculated. Most banks add compound interest daily (gets them more money).

The wikipedia article has a nice example of the equation used by US banks. You can code that up.

Answer 3

The algorithms are well shown and discussed here (in Javascript) -- implement exactly the same algorithms in Excel's VBA, Javascript, Ruby, whatever, and you'll get pretty much the same results!-)