1

I'd like to setup an Excel spreadsheet to calculate the APR for a loan with an irregular payment schedule. For example a 12 month loan with 11 payments of $100 and a final payment of $500. I know how to get the APR for a schedule with identical payments, but can't figure out an irregular schedule. I'd prefer to use built-in formulas but can use VBA if that's the only way.

The RATE function requires all the payments to be the same amount. For example, to calculate the APR on a $258,510 loan with 360 identical monthly payments of $1,171.92, the formula would be RATE(360,-1*1171.92,258510)*12

But how would you use RATE with a loan of the same size having with 180 payments scheduled as follows: 179 payments of 1171.92 and 1 payment of $140,374.96)?

In response to the comments - I'm not asking for the formula but how to implement it - specifically, whether it's possible to use Excel's built-in tools to do so. If you really want the formula, it's quite complex. You can see it here: http://www.gpo.gov/fdsys/pkg/CFR-2013-title12-vol3/pdf/CFR-2013-title12-vol3-part226-appJ.pdf for an example close to what I'm trying to do, look at either paragraph (3) "Single advance transaction with an odd final payment" or paragraph (6), example (iii) "Mortgage with varying payments". The math is over my head, which is why I was hoping Excel had a function to do it.

Improve this question
3
  • You need to show some research effort into your questions. For example, what formula would you write out on paper to get the result you need, given values x, y and z? Then, we could help
    – Canadian Luke
    Jul 21, 2014 at 16:49
  • To me this looks more as a question about the formula rather than being Excel specific.
    – Hannu
    Jul 21, 2014 at 18:23
  • @user348514 and user348492 – if you are the same person, pick one account (preferably the registered one, user348514) and use it exclusively. This will allow you to edit your own question without having your edit treated as a “suggestion” that needs to be reviewed. Better yet, request to have your accounts merged: see here or here for instructions.
    – Scott - Слава Україні
    Jul 21, 2014 at 20:16

2 Answers 2

Reset to default
0

Myabe this link will help?? Or maybe another template on that page?? http://office.microsoft.com/en-us/templates/biweekly-mortgage-payment-amortization-TC001056618.aspx?CategoryID=CT011815531033&AxInstalled=1

Improve this answer
1
  • At the moment, this post is essentially only a link. To make sure the answer remains useful even if the link breaks, please edit it to include the relevant information. (Your answer was discovered in an effort to repair or remove old link-only answers.)
    – Ben N
    Jan 10, 2016 at 4:47
0

I think I figured it out. It's surprisingly easy. You just create a range with the payments and use the IRR function then multiply by 12 to get the annual rate.

Improve this answer

Not the answer you're looking for? Browse other questions tagged .