I am trying to reproduce in Google Docs the calculations underlying the sample mortgage Closing Disclosure provided by the CFPB at:
http://files.consumerfinance.gov/f/201311_cfpb_kbyo_closing-disclosure.pdf
That document describes a mortgage with the following parameters:
Loan Amount: $162000
Annual Interest Rate: 3.875%
monthly PMI: $82.35
total loan costs: $4694.05
prepaid interest: $279.04
and summarizes it as follows (page 5):
Total Payments: $285803.36
Finance Charge: $118830.27
Amount Financed: $162000.00
Annual Percent Rate: 4.174%
Total Interest Percent: 69.46%
Almost everything I calculate seems to agree but I can't get the formula right for the effective APR (4th line of summary).
I currently calculate it as follows
=100*12*rate(12*30, -1*(4694.05+279.04+162000+-1*cumipmt(0.03875/12, 30*12, 162000, 1, 30*12, 0)+82.35*80)/360, 162000, 0, 0)
This comes out to 4.218%, not 4.174% as published.
What am I missing?
The code I'm using is here:
https://docs.google.com/spreadsheets/d/1VQshp3A55brVv17eS9REdBjBUG0EmwTrcwhgXBVK8m8/edit#gid=0