APR Calculation

[jeffreysdunn]

I have data in two columns, Column A is Date, Column B is payment information (first cell contains loan amount). Is there an Excel function that computes the implied APR that amortizes that loan?

Date Payment
9/7/2018 $(1,000.00)
9/21/2018 $165.00
10/5/2018 $165.00
10/19/2018 $165.00
11/2/2018 $165.00
11/16/2018 $190.00
11/30/2018 $181.75
12/14/2018 $173.50
12/28/2018 $165.25
1/11/2019 $157.00
1/25/2019 $148.75

 

[Dave Patton]

What is your estimate of the rate?
Did you prepare an amortization schedule?

 

[JGordon11]

Those data imply an extremely high APR (over 10% every two weeks)

Book1
	A	B	C	D
1	Date	Payment	IRR for 14 day period	APR from IRR 14 day
2	9/7/2018	-1000	10.781%	1343.005%
3	9/21/2018	165
4	10/5/2018	165
5	10/19/2018	165
6	11/2/2018	165
7	11/16/2018	190
8	11/30/2018	181.75
9	12/14/2018	173.5
10	12/28/2018	165.25
11	1/11/2019	157
12	1/25/2019	148.75
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IRR(B2:B12)
D2	D2	=(1+C2)^(365/14)-1

using an initial loan value of -1650 gives a more realistic rate of return

Date	Payment	IRR for 14 day period	APR from IRR 14 day
9/7/2018	-1650	0.291%	7.867%
9/21/2018	165
10/5/2018	165
10/19/2018	165
11/2/2018	165
11/16/2018	190
11/30/2018	181.75
12/14/2018	173.5
12/28/2018	165.25
1/11/2019	157
1/25/2019	148.75

 

[Dave Patton]

Try preparing an amortization schedule and then use Data Goal seek.
I get a little higher than 281%

 

[jeffreysdunn]

Try preparing an amortization schedule and then use Data Goal seek.
I get a little higher than 281%

Thank you, those are just made up numbers. I actually did what you suggested, but I'm preparing this for presentation (in court) and don't want to go through the explanation of goal-seek and iterative processes. The Amortization schedule works well but I was hoping there was an APR function in Excel.

 

[jeffreysdunn]

Try preparing an amortization schedule and then use Data Goal seek.
I get a little higher than 281%

Thank you, those are just made up numbers. I actually did what you suggested, but I'm preparing this for presentation (in court) and don't want to go through the explanation of goal-seek and iterative processes. The Amortization schedule works well but I'm just a little

 

[Dave Patton]

Why not prepare the amortization schedule and then you can just state the interest charged was x%,?

 

[jeffreysdunn]

Well I did that, but was looking for a second way to demonstrate the point.