• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Devaka Cooray
  • Ron McLeod
  • paul wheaton
Saloon Keepers:
  • Tim Moores
  • Piet Souris
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Frits Walraven
  • Scott Selikoff

Need to program a financial APR calculation given a series of Bi-Weekly payments

 
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

I have a working program that will compute APR, using Newton's method, for a regular payment and term.

What I need to do now is different and I cannot find any code or other financial resource other than the "APRWin" Windows EXE program on the treasury site (no source code).

Here's an example:

Loan $3,463.22
Payments $125
Number of Payments 33
Unit 14 days
1st Payment in a series of 32, starts 1 Unit out $125
33rd payment due 33 units out: $21.22

---

I need to compute the APR (which is 24.1021% for this example).


Just wondering if there are any computational financial wizards out there who know how to calculate the APR given these biweekly payments with odd payment offssets,

Please let me know!

Thanks,

-- mike
 
Marshal
Posts: 76845
366
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I have that sort of thing to calculate, I let my spreadsheet calculate the internal rate of return (@IRR, =IRR, etc.) and do some exponentiation on it. If the payments are spread out by weeks it would look something like this:-It is giving IRR as 1.06410% and APR as 73.4

[edit] I didn't notice the last payment of $21, which will reduce both results.
 
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Define two functions:

A(n, r) = (1 + r) ^ -n
a(n, r) = (1 - A(n, r)) / r if r != 0, n otherwise.

Here is n = number of periods, and r is the interestperunage per period (perunage, so when the interest rate = 2%, then r = 0.02).

A(n, r) and a(n, r) are, given n, monotone decreasing functions of r.

Now form this equation:

3,463.22 = 125 * a(32, r) + 21.22 * A(33, r)

To solve this for r with Newton-Raphson is possible, but the derivatives are not very attractive. So a simple iteration will suffice. Start with the interval of [0, .5], take r = 0.25 and when the RHS is bigger than the LHS, r is too low, and vice versa.

For a little background, see some info
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:Define two functions:

A(n, r) = (1 + r) ^ -n
a(n, r) = (1 - A(n, r)) / r if r != 0, n otherwise.

Here is n = number of periods, and r is the interestperunage per period (perunage, so when the interest rate = 2%, then r = 0.02).

A(n, r) and a(n, r) are, given n, monotone decreasing functions of r.

Now form this equation:

3,463.22 = 125 * a(32, r) + 21.22 * A(33, r)

To solve this for r with Newton-Raphson is possible, but the derivatives are not very attractive. So a simple iteration will suffice. Start with the interval of [0, .5], take r = 0.25 and when the RHS is bigger than the LHS, r is too low, and vice versa.

For a little background, see some info



Hi Piet,

That's cool. I am not matching the expected values however.

So, my approach was to bring the RHS to the LHS and set equation equal to zero. Then test for the "loopValue" still being greater than tolerance. The way I understand it, is that when we get the proper r, within tolerance, the formula should be near zero.

In my code below, using the expected interest rate (which should make the formula zero, right?), I instead get: 2945.093445245364

Perhaps I mis-implemented your functions, but I've checked them a couple times.

Here's my Java code below.

Look forward to your comments.





 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Mike,

yes, there is some misunderstanding. I meant a simple binary search.
So,

Note that both functions A and a are present in Excel (and in LibreOffice), so you can either put that equation in excel, with r being some cell, and use target solving, or you can similarly put all the payments (33 of 'm) in a column, and again use target solving.

It's been years since I last did that in excel, so the exact details are a little vague now, but if you got the java code running, you can try to solve it with a spreadsheet, see if you get the same results.


 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:hi Mike,

yes, there is some misunderstanding. I meant a simple binary search.
So,

Note that both functions A and a are present in Excel (and in LibreOffice), so you can either put that equation in excel, with r being some cell, and use target solving, or you can similarly put all the payments (33 of 'm) in a column, and again use target solving.

It's been years since I last did that in excel, so the exact details are a little vague now, but if you got the java code running, you can try to solve it with a spreadsheet, see if you get the same results.




Since your code above is out of context, I substituted in in to my logic, but I see an apparent problem with it.

Namely, you set upper and lower bound to 0 and .5.

Then you do this: r = (lowerbound + upperbound) / 2;

However, the next if statement resets r to either lowerbound (0) or to upperbound (0.5).

Therefore, this code never completes and is stuck at the same RHS value each time.

Am I confused again?

Do you have a working example from the code I started?

Here's the updated method that uses your code:




Thanks,
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think I found the problem with your posted code. Namely, the assignments were backwards in the IF statement.

So the code is actually (I believe):



Now, the code converges quickly, but not to the correct result.

The correct result should be 24.1021%

But, this logic generates: 25.00009536743164%

===

The treasury site has a program called "APRWin" that gets the right result, just no source code!

Thanks,

-- mike
 
Saloon Keeper
Posts: 26275
186
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, Am I missing something? This reads to me like a mortgage with a down payment and a short payment at the end. The only thing offbeat from what the company I used to work for did millions of times a day is the payment frequency.
 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Mike,

first an apology: I wrote in the code snippet

which of course is a blunder, Should be:


I don't know how you obtained a percentage of over 24, that is way more than I get.

This is the code that I used:

 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:hi Mike,

first an apology: I wrote in the code snippet

which of course is a blunder, Should be:


I don't know how you obtained a percentage of over 24, that is way more than I get.

This is the code that I used:



No worries. I had changed the code already as you did and got the same result (0.009270191192626953).

Here's the Treasury site where you can download their program.

https://www.occ.gov/publications-and-resources/tools/aprwin/index-aprwin.html

I must be missing something about the offsets with the dates or something.

Maybe it's a BiWeekly thing?


Thanks,
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's another example:

Example 2

Loan $2995
Payment 125
Number of payments 26
Unit 14 days
1st Payment made, in a series of 25, 1 unit and 7 days out
Last payment made 26 units and 7 days out $88.97

From that APRWin calculates: 13.4076% APR


===

The code above calculates:  0.5354404449462891%
 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The formula that calculates loopResult must be changed a little.

You see, the timeunit is 14 days, but the payments are all done on a timeunit + .5. The first payment takes place at 1.5 (ie 21 days), the second at 2.5, et cetera, and the final payments at 26.5. Did I understand correctly?

The loopResult must now be calculated as:

and I get
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:The formula that calculates loopResult must be changed a little.

You see, the timeunit is 14 days, but the payments are all done on a timeunit + .5. The first payment takes place at 1.5 (ie 21 days), the second at 2.5, et cetera, and the final payments at 26.5. Did I understand correctly?

The loopResult must now be calculated as:

and I get



I appreciate your help, but your results don't match what APRWin gets or what the client expects.

I'll post back if/when I figure this out.

Thanks,

-- mike
 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
With that percentage (13....), the loan should be 2787.68, instead of 2995.

I can only guess that our assumptions about times of paying differ.

But you're welcome.
 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I screwed up and you are right. I apologize

I calvculated the 14 days interest rate r, (noting wrong with that), but then I calculated the year rate as (1 + r) ^12 - 1, i.e. as if the period was months, instead of 14 days.

So the outcomes I got now are:

interest per period r: 0.005153656005859375
interest on yearbasis: 0.1429937945220212    // (1 + r) ^26 - 1
int on year: 0.13399505615234375  // 26 * r
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:With that percentage (13....), the loan should be 2787.68, instead of 2995.

I can only guess that our assumptions about times of paying differ.

But you're welcome.



What the client wants is APR calculated via bi-weekly, not the annual APR.

I've attached an actual example from the APRWin program.

I'm not sure how to convert from what you did to Bi-Weekly, but maybe that's the difference?

If you're able to match the results below, please let me know.

Appreciate all your input and enjoy learning this new stuff (when I eventually learn it, that is...).

-- mike
APRWin-Example.png
[Thumbnail for APRWin-Example.png]
 
Tim Holloway
Saloon Keeper
Posts: 26275
186
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mike London wrote:What the client wants is APR calculated via bi-weekly, not the annual APR.



That's a pretty good trick, considering that APR stands for Annual Percentage Rate. What they want is the periodic percentage rate, I think.

APR would probably be tricky on a 14-day payment schedule, since a year isn't an exact number of weeks long. Meaning that if you computed the annual rate within a given year of the loan, the number of periods/annum would wobble depending on what year it was.
 
Campbell Ritchie
Marshal
Posts: 76845
366
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:. . . a year isn't an exact number of weeks long. . . .

You are allowed to make typical assumptions, e.g. loan taken out of 31st December and first payment on 14th January, then every two weeks exactly, and exactly fifty‑two weeks in the year. The rules for predicting an APR also allow you to overestimate the APR by ≤ 1.0% and underestimate by ≤ 0.1%. That is to allow for the fact that one cannot predict every repayment schedule in advance. You can predict bi weekly payments by using a weekly schedule with alternate payments of 0.00.
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks to all for the replies.

I'll be researching how to get the APRWin- and client-expected values and will post back once I figure it out

-- mike
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:

Tim Holloway wrote:. . . a year isn't an exact number of weeks long. . . .

You are allowed to make typical assumptions, e.g. loan taken out of 31st December and first payment on 14th January, then every two weeks exactly, and exactly fifty‑two weeks in the year. The rules for predicting an APR also allow you to overestimate the APR by ≤ 1.0% and underestimate by ≤ 0.1%. That is to allow for the fact that one cannot predict every repayment schedule in advance. You can predict bi weekly payments by using a weekly schedule with alternate payments of 0.00.




It's "APR" ... given biweekly payments.
 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:Well, I screwed up and you are right. I apologize

I calvculated the 14 days interest rate r, (noting wrong with that), but then I calculated the year rate as (1 + r) ^12 - 1, i.e. as if the period was months, instead of 14 days.

So the outcomes I got now are:

interest per period r: 0.005153656005859375
interest on yearbasis: 0.1429937945220212    // (1 + r) ^26 - 1
int on year: 0.13399505615234375  // 26 * r




I think your output looks correct!


Would you please post your updated code?

Using my original code (see below), I get for the two examples below (one looks correct!):

0.009270191192626953
0.005354404449462891


Multiplying these two values by 26 (assuming that works in all cases?), I get:

0.1392145157  (close to your correct answer but not quite there) -- should be: .134076
0.2410213873 -- gulp, this one looks correct!

I'm off by a bit.

Here's what I think you're saying, but I don't get your exact results (so close!!!):



I need to make this work for other cases too.

Thanks,

- mike
 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Mike,

this looks good, but there is one snag: the two cases are not similar, meaning that you cannot use the same formula for the RHS in line 44. I'll explain this n my next post (that I just finished), that is meant to give you some idea of what you can expect when entering the world of financial maths.

By he way: why do you return a String instead of a double?
 
Piet Souris
Saloon Keeper
Posts: 5157
207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Mike,

I was teaching a course "Introduction to Actuarial Mathematics" in the 90's, and the first three months were all about loans and determining their values at any time. And in general, people did not find that easy.

The principle is always: the present value of past and future income is always equal to the present value of past and future outgo.

So, it boils down to determining the present value. The two formulas I presented simplify this often.

In the first exercise, with a normal bi-weekly payment, the present value (PV) of the outgo is the loan, the PV of the income = payment * a + restPayment * A. Iterating, we get the value of the interest rate for this bi-weekly period.

The second example was a little bit more challenging. The outgo is still the loan, at t = 0, the income is at times 1.5, 2.5,... 26.5. These are not integer times, so what I did was to determine the PV at time t = 0.5. We get the same formula as in the first exercise. But now we have an outgo PV at t = 0, and an income PV at t = .5. To remedy this, I discounted the income PV over a half timeunit to t = 0.

Calculating the APR is now a triviality. I take a frequency of 26 payments in a year. Now, there are two APRs involved. If r = interestperunage per timeunit (2 weeks), then what we call the real APR = (1 + r) ^26 - 1.
However, to the public the so called apparent APR is reported, and that is simply 26 * r. Guess which of the two is lower...

This is what you can expect in your quest for more knowledge. But I found this always very nice maths.


 
Mike London
Bartender
Posts: 1899
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Piet Souris wrote:hi Mike,

I was teaching a course "Introduction to Actuarial Mathematics" in the 90's, and the first three months were all about loans and determining their values at any time. And in general, people did not find that easy.

The principle is always: the present value of past and future income is always equal to the present value of past and future outgo.

So, it boils down to determining the present value. The two formulas I presented simplify this often.

In the first exercise, with a normal bi-weekly payment, the present value (PV) of the outgo is the loan, the PV of the income = payment * a + restPayment * A. Iterating, we get the value of the interest rate for this bi-weekly period.

The second example was a little bit more challenging. The outgo is still the loan, at t = 0, the income is at times 1.5, 2.5,... 26.5. These are not integer times, so what I did was to determine the PV at time t = 0.5. We get the same formula as in the first exercise. But now we have an outgo PV at t = 0, and an income PV at t = .5. To remedy this, I discounted the income PV over a half timeunit to t = 0.

Calculating the APR is now a triviality. I take a frequency of 26 payments in a year. Now, there are two APRs involved. If r = interestperunage per timeunit (2 weeks), then what we call the real APR = (1 + r) ^26 - 1.
However, to the public the so called apparent APR is reported, and that is simply 26 * r. Guess which of the two is lower...

This is what you can expect in your quest for more knowledge. But I found this always very nice maths.




Thanks.

Since I need a way to use a single formula approach (as APRWin does) without needing to change formulas for each case, I need to do more research.

I called a couple colleges looking for grad students who wanted to make a few extra bucks, but nada...

Maybe upwork? We'll see.

Appreciate all your help.

-- Mike
 
Blueberry pie is best when it is firm and you can hold in your hand. Smell it. And smell this tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic