Does anyone happen to have an Excel spreadsheet to assist with calculating APR's for Ads? I have one, but it does not incorporate the blended rate you have to use when you have a discounted rate (i.e. on ARM's).

THANK YOU!

I use the OCC's APR calc. It is very good if you know how to use it. Who is your regulatory agency? And if anyone does happen to have one on Excel I would love to see how you did it. Becasue using the model calc format in Reg Z is sooo easy! Yea right!

Tina A Sweet-Williams
Gold Country Bank, NA
Marysville, Ca. 95901
mailto:tsweet@goldcountrynb.com

Yes, I use APR Win to verify APR's for example on our disclosures. However, what I am looking for is an Excel spreadsheet that will actually calculate APR's (for use when we are developing a marketing piece) for example on a discounted ARM program where you are required to use a blended rate.

If you are going to use this a lot and believe it is worthwhile, I'd contact a vendor about writing a special program for you. While I am certain there are several, this does require specialization and I'll plug APR Systems. Richard Insley is a common poster here so you may know his name. He wrote the APRX program distributed at compliance schools and which I use in audits. He does the exact projects you are requesting.

While I have done some APR calcs, mostly for Appendix D loans, the Fed calendar and automation requirements can be tedious and difficult. If this will be a primary source it should be easy to use and accurate. Get a pro.

Thanks, Andy, your check is in the mail. (Just kidding )

In fact, I DO develop all kinds of regulatory calculation solutions, including ones that you can write into your own software systems. During my years as a bank C/O, I paid in installments for the lesson that automation beats training. Custom programs allow you to present tools to your staff in the manner, format and language that's the best fit for your bank's products, operations and corporate culture. Good tools pay for themselves by reducing the frequency of errors, reducing or eliminating training time, and improving service quality.

Although I'm a big fan of sharing in general and BOL in particular, sometimes "FREE" is not the best price.

Were you ever able to obtain an excel spreadsheet for apr calculations? I am trying to find one for use when compiling an internal rate sheet. Arms and Fixed Rate Mortgages

Were you ever able to obtain an excel spreadsheet for determing aprs for arms and fixed rate mortgages. I am looking for one to use for compiling an internal weekly rate shate for distribution to loan originators.

Nope. Still don't have one for those darn ARM's. Will let everyone know if I get one, though.

It's my understanding that you use two excel formulas to come up with the APR:

=PMT(I/12,N*12,PV) - to come up with the payment (I = rate, N = term , PV = present value)

=RATE(N*12,PMT,PV)*12 - to come up with the APR

PV calculation =-Financed+(Points*Financed)+Fees

You may want to try this.

This is a step. Now all you need to add are:
- prepaid finance charges that are financed
- long and short odd days
- balloon payments
- PMI or FHA insurance premiums and termination
- discount and premium ARM pricing
- buydowns
- step rates
- skip payments
- construction options
- multiple-advances
- debt cancellation options
- any combination of the items above

Richard only thinks you need that "stuff" because he has been studying Reg. Z and the errors from it for 35 years.

A less desirable option is to put an amount equal to all the finance charges into an escrow account and wait for the legal documents. The law of averages says you won't get sued by everyone, so you should have enough there to cover your costs.

Seriously, anytime I used XL, I'd double check the results. Odd days was always a bear for me.