-1

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

user3931014
  • 137
  • 1
  • 6
  • 1
    Questions asked here must contain all relevant content here, not at another site. See [ask] and [mcve]. If the relevant content is located off-site, and that off-site location is unavailable (moved, deleted, etc.), the question has no value for future readers. As currently written, there is no relevant content here. (In addition, *My code won't work. Figure out why* is far from being a *specific question*.) – Ken White Jul 11 '17 at 03:09
  • Hi Ken White, part of the scope of my question is to determine which subset of the numbers provided in the first link are relevant. I certainly could list all of them, but that does not seem minimal. I would upvote a response that gave me a clue as to which numbers my code has ignored are in fact relevant. To address your comment I have included all inputs to my calculation that I would expect to be relevant. But I may be wrong -- and that is exactly my question. Thanks for making my question clearer. – user3931014 Jul 11 '17 at 04:31

1 Answers1

0

APR has many nuances. It's primarily a customer-facing metric, so banks feel pressured to keep the number low.

Many institutions assume 12 months of 30 days each; some take other approaches. There's different ways of treating leap years, Escrow, etc.. Since you are close, but not exact, likely this institution has a non-standard APR calculation.

ddg
  • 1,090
  • 1
  • 6
  • 17
  • The institution is the Consumer Financial Protection Bureau, I think they are supposed to publish the standard APR calculation, whatever that is. – user3931014 Jul 11 '17 at 04:09