# APR Calculation



## jeffreysdunn (Dec 22, 2022)

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 (Dec 22, 2022)

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


----------



## JGordon11 (Dec 22, 2022)

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

Book1ABCD1DatePaymentIRR for 14 day periodAPR from IRR 14 day29/7/2018-100010.781%1343.005%39/21/2018165410/5/2018165510/19/2018165611/2/2018165711/16/2018190811/30/2018181.75912/14/2018173.51012/28/2018165.25111/11/2019157121/25/2019148.75Sheet2Cell FormulasRangeFormulaC2C2=IRR(B2:B12)D2D2=(1+C2)^(365/14)-1

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

DatePaymentIRR for 14 day periodAPR from IRR 14 day9/7/2018-16500.291%7.867%9/21/201816510/5/201816510/19/201816511/2/201816511/16/201819011/30/2018181.7512/14/2018173.512/28/2018165.251/11/20191571/25/2019148.75


----------



## Dave Patton (Dec 22, 2022)

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


----------



## jeffreysdunn (Dec 22, 2022)

Dave Patton said:


> 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 (Dec 22, 2022)

Dave Patton said:


> 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 (Dec 22, 2022)

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


----------



## jeffreysdunn (Dec 22, 2022)

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


----------

