Does anyone have the APR formula built into an excel spreadsheet?

I know how to create formulas in Excel, but the APR formula from Reg Z has me very confused.

Any help would be appreciated.

One suggestion - give it up unless you have a year or two to waste. If it was possible, it would already be on the toolbar.

There is an iterative formula in the Thorndike Encyclopedia of Banking and Financial Tables that can give you an APR on payment loans. I have found it to be pretty accurate. You have to provide the Prncipal Amount, Periodic Payment amount, and the number of payments. The result of the formula is the 'periodic interest rate' so you would multiply it by 12 on a monthly payment loan to get the annual rate.

Use the function RATE and put all the payments in the boxes designated in the formula. This will not work for a line of credit, but if you properly calculate all the payments and put the right number of them (59 and 181 for a 5-year adjustable with 20 year maturity, for instance), you will get the correct APR. Better off talking to Mr. Insley or downloading APRWIN from th OCC. Then you don't have to copy and paste the payment amount hundreds of times to create the correct payment streams.

I agree principally with rlclarey. The stuff is just too involved and complicated to take a chance on it.

Whatever you do, do not use the RATE function if you have any calendar anomolies (i.e. long or short first period)! It assumes all equal periods.

There are too many accurate and economical tools out there to be messing around with this. To quote Dan Ackroyd in Trading Places, "This is a career decision here."

Good luck!

For a long first period, wouldn't you just decrease the amount financed by the interest accrued over the extra days?

That being asked, I'll agree that Excel is as good a tool for calculating APRs as a pitchfork would be for eating soup. You might be able to pull it off with certain kinds of soup, but it would be a heck of a lot more work than just using the proper tool.