Finance formula question

[Juan Pablo González]

Hey guys,

I think I have a fair understanding of the finance formulas, but I'm kind of confused today, so I could use some help.

Let's say I have a standard loan, Term = 60 months, APR = 8%, Financed amount = $20,000. This gives me the pmt of $405.53 which is ok.

The issue is that I have to include a "prepaid" fee, and then calculate the "real" APR rate, based on that. Like this:

Book2
	A	B	C	D
1	Term	60	60
2	Finance	$20,000.00	$20,000.00
3	Prepaid	$0.00	$100.00
4	BaseAPR	8.000000%	8.000000%
5	PMT	$405.53	$407.56
6	DiscAPR	8.000000%	8.211521%
Sheet1

Formulas:

C1: =$B$1
C2: =$B$2
B5:C5: =PMT(B4/12,B1,-B2-B3)
B6:C6: =RATE(B1,B5,-B2)*12

Now, the issue that I have is, having the Term (60), the Financed amount ($20,000), Prepaid fee ($100), the real APR (8.2115%) and PMT ($407.56), how can I calculate the "original" APR ? (8.000%)

I found this but it gets off with low financed amounts and terms:

Book2
	E	F	G	H
1	Term	60
2	Financed	$20,000.00
3	Prepaid	$100.00
4	DiscAPR	8.211521%
5	PMT	$407.56
6
7		$405.52
8		7.999%
Sheet1

Formulas:

F1: =$B$1
F2: =$B$2
F4: =C6
F5: =PMT(F4/12,F1,-F2)
F7: =PMT(F4/12,F1,-F2+F3)
F8: =RATE(F1,F7,-F2)*12

the 7.999% is close enough rounding to 2 decimals, but if I use $5000 for the financed amount, I get off by too much !

 

[fairwinds]

Hi Juan!

Must it not be

=RATE(F1,F5,-F2-F3)*12

Book1
	E	F	G	H
1	Term	60
2	Financed	20000
3	Prepaid	100
4	Disc APR	8.21152%
5	PMT	407.56 kr
6
7	Base APR	8.00000%
8
Sheet1

 

[Juan Pablo González]

yep ! seems to be... man, I've spent 8 hours on this... I knew it *had* to be simple... I was doing a goal seek algorithm already...

So, thanks !

 

[XL-Dennis]

Hi CM Juan,

I've spent 8 hours on this

I sincerely hope that You don´t charge the client for the time :wink:

 

[Juan Pablo González]

Hi CM Juan,

I sincerely hope that You don´t charge the client for the time :wink:

Actually, it's for my "work work" so *it's included*.... :wink: