This is in the US. Fixed rate loan and the first payment is one month after receiving the funds. Actual / 360, interest paid for the actual number of days in each month.
I can confirm that those facts are probably correct.
Refer to "japes amort sched.xls", downloadable from https://app.box.com/s/tr1i738yxcb5d0sf5ssh. Ignore any preview errors and just download.
When I set up an amortization schedule (below) with the monthly payment of 58,360.23 and monthly interest based on actual/360, Solver determines that the actual annual interest rate is between 3.99045988210% and 3.99045988522%.
Note that it rounds to 3.99%. Such differences between documented and actual interest rates are permitted by US "Truth in Lending" regulations (Reg Z).
Also note that we cannot calculate the actual interest rate using Excel RATE because the function effectively calculates a monthly rate based on 30/360.
And we cannot calculate the actual payment using Excel PMT, in part because we did not know the exact interest rate used by the bank, and in part because the function calculates payment to the full machine precision, not the real-world rounding to 2 decimal places.
The following demonstrates how to set up the amortization schedule.
| A
| B
| C
| D
| E
| F
| G
| H
| I
|
1
| Loan
| 11,000,000.00 |
| Pmt# |
| Pmt | Int | Bal
|
|
2
| Term | 300 | months |
| 6/1/2014 |
|
| 11,000,000.00
|
|
3
| Annl_rate | 3.99045988365865% |
| 1 | 7/1/2014 | 58,360.23 | 36,579.22 | 10,978,218.99 |
|
4
| Actl pmt | 58,360.23 |
| 2 | 8/1/2014 | 58,360.23 | 37,723.68 | 10,957,582.43 |
|
5
| Min rate | 3.99045988210% |
| 3 | 9/1/2014 | 58,360.23 | 37,652.77 | 10,936,874.97 |
|
6
| Max rate | 3.99045988522% |
| 4 | 10/1/2014 | 58,360.23 | 36,369.30 | 10,914,884.04 |
|
|
|
|
|
|
|
|
|
|
|
300
|
|
|
| 298 | 4/1/2039 | 58,360.23 | 597.55 | 116,134.42 |
|
301
|
|
|
| 299 | 5/1/2039 | 58,360.23 | 386.19 | 58,160.38 |
|
302
|
|
|
| 300 | 6/1/2039 | 58,360.23 | 199.85 | 0.00 |
|
303
|
|
|
|
|
|
|
| 2.59E-07 | Actl_bal
|
<tbody>
</tbody>
Formulas:
E3: =EDATE($E$2,D3)
F3: =$B$4
G3: =H2*(E3-E2)*$B$3/360
H3: =H2+G3-F3
Copy E3:H3 down through row 302.
-----
Note that for comparison purposes, the Reg Z annual interest rate (based on 30/360) is 4.04902518141567% = 12*RATE(300,58360.23,-11000000).
The difference between that and the bank's actual annual interest rate (based on actual/360) is about 0.059%, which is within the 0.125% difference permitted by Reg Z.