Actual loan rate with fees calculation

[juju]

I have been struggling with this for days. Several methods found online give me different results. Thought I should throw it out here to see what the folks here can come up with.

I am looking to setup in excel, a calculation that shows the actual interest rate on a loan, after origination fees or any other fees have been applied.

Loan Info:

1. Amount: $1M
2. Rate: 3 %. p.a.
3. Fees : 5%
3. Term 10 years

Other important loan features:

1. Fees are payable upfront and deducted from the original loan amount before disbursement
2. Loan payment starts after year 2 ( 2 year grace period )
3. Loan must be fully paid off in year 10 ( means 8 years from beginning of payments )
4. Assume payments are monthly


What is the actual rate in this scenario? How can I set this up in excel to calculate this ?

 

[Dave Patton]

I am making a few guesses.
We do not know the rules and regulations in your jurisdiction.
You did not provide an example of what you tried.
The following may provide some help.
Does 3.88% sound reasonable?

Prepare an amortization schedule showing what payments will pay the loan in 10 years.
Then use Goal Seek to to determine what Rate will yield the final result of 0 with the net proceeds.

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00	$1,061,757.04	$12,453.96
2	Fees	5%
3	Rate	3%
4	Term	10
5		Interest	Payment	1,000,000.00
6	1	2,500.00		1,002,500.00
29	24	2,647.77		1,061,757.04
30	25	2,654.39	12,453.96	1,051,957.48		$12,453.96
31	26	2,629.89	12,453.96	1,042,133.41
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
E1	E1	=FV(B3/12,24,,-B1)
F1	F1	=PMT(B3/12,8*12,-E1)
F30	F30	=PMT(0.03/12,8*12,-D29)
B6,B29:B31	B6	=D5*$B$3/12
D6,D29:D31	D6	=D5+B6-C6

 

[juju]

Hi - I am finding it hard to follow what you mean. I see what each formula does but not sure what your suggestion for 3.88% means?

 

[Dave Patton]

Your question stated "a calculation that shows the actual interest rate on a loan".
Is 3.88% the result that you expected?

1. Prepare an amortization schedule showing what payments will pay the loan of 1,000,000 in 10 years; see the post.
2. You actually received 950,000; there was a 50,000 fee.
3. Use Goal seek to determine the rate with 950,000 in D5 and the payments (1,195,580.16) per schedule 1
move to the last row D125
With Goal Seek change D125 to 0 by changing B3 the rate
When process completes press OK
The result shows the 3.88%

 

[Dave Patton]

Initial information

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00
2	Fees	5%			Total payments	$1,195,579.74
3	Rate	3%
4	Term	10
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
F2	F2	=PMT(B3/12,8*12,-FV(B3/12,2*12,,-B1))*8*12

Edit the formula to B1 - 50000 or change reference to D1
The formula will yield smaller results.
Click on F1 and run Goal Seek
Change value to 1,195,579.74 by changing B3
B3 will change to 3.88%

Final result

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00
2	Fees	5%			Total payments	$1,195,579.74
3	Rate	3.88%
4	Term	10
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
F2	F2	=PMT(B3/12,8*12,-FV(B3/12,2*12,,-D1))*8*12

 

[juju]

Initial information

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00
2	Fees	5%			Total payments	$1,195,579.74
3	Rate	3%
4	Term	10
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
F2	F2	=PMT(B3/12,8*12,-FV(B3/12,2*12,,-B1))*8*12

Edit the formula to B1 - 50000 or change reference to D1
The formula will yield smaller results.
Click on F1 and run Goal Seek
Change value to 1,195,579.74 by changing B3
B3 will change to 3.88%

Final result

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00
2	Fees	5%			Total payments	$1,195,579.74
3	Rate	3.88%
4	Term	10
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
F2	F2	=PMT(B3/12,8*12,-FV(B3/12,2*12,,-D1))*8*12

I am having trouble running the goal seek

 

[Dave Patton]

Your preliminary value for F2 1,135.800.75

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00
2	Fees	5%			Total payments	$1,135,800.75
3	Rate	3.00%
4	Term	10
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
F2	F2	=PMT(B3/12,8*12,-FV(B3/12,2*12,,-D1))*8*12

select cell F2
Run Goal Seek
Change value to 1,195,579.74
By changing B3
Goal Seek will determine a new value 3.88% for cell B3
Press OK

T202405.xlsm
	A	B	C	D	E	F
1	Amount	1,000,000.00	Net	950,000.00
2	Fees	5%			Total payments	$1,195,579.74
3	Rate	3.88%
4	Term	10
4a

Cell Formulas
Range	Formula
D1	D1	=B1*0.95
F2	F2	=PMT(B3/12,8*12,-FV(B3/12,2*12,,-D1))*8*12