Formula for calculating interest

[Stclements1]

I wish to borrow 10k
I have arranged that I will pay back the 10k over 11 months. Month 1 no payment then month 2-11 1000 per month capital payment and an interest payment of 100 per month. By month 11 I would have paid the full 10k back and also 1000 in interest. My question is, is there a formula that will give me the annualised rate of return for the creditor based on this payment program?

 

[footoo]

Have a look at the XIRR function (works out at an IRR of 23.46%).

 

[Stclements1]

Will do many thanks.

 

[AhoyNC]

An annualized rate of 23.46% seems high when rate was 10% for 11 months.

Using the above I get an annualized rate of about 10.96%

Book1
	A	B	C
1
2	Rc	10%	11,000/10,000
3	N	11	Time to pay off
4	P	12	months
5
6	Ra	10.9572%
Sheet2

Cell Formulas
Range	Formula
B6	B6	=((1+0.1)^(12/11))-1

 

[dcardno]

Using the above I get an annualized rate of about 10.96%
No, I think the prior calculation of ~23% is correct, depending on the details of "no payment in the first month." If that means the loan is advanced on (say) January 1, and no payment is due until Feb 28, and then on the ends of the months following, then the return is ~ 19.6%.

 

[Dave Patton]

I also thought the rate looked high.
I built a table and used Goal Seek.
Please review the dates and results.

T202005a.xlsm
	A	B	C	D	E	F
1	31-Dec-19	10,000.00				17.90%
2	31-Jan-20	0.00	152.00	10,152.00
3	29-Feb-20	(1,100.00)	144.36	9,196.36
4	31-Mar-20	(1,100.00)	139.79	8,236.15
5	30-Apr-20	(1,100.00)	121.15	7,257.31
6	31-May-20	(1,100.00)	110.31	6,267.62
7	30-Jun-20	(1,100.00)	92.20	5,259.82
8	31-Jul-20	(1,100.00)	79.95	4,239.77
9	31-Aug-20	(1,100.00)	64.45	3,204.22
10	30-Sep-20	(1,100.00)	47.13	2,151.35
11	31-Oct-20	(1,100.00)	32.70	1,084.05
12	30-Nov-20	(1,100.00)	15.95	0.00
4d

Cell Formulas
Range	Formula
C2	C2	=B1*(A2-A1)*$F$1/365
D2	D2	=B1+C2+B2
C3:C12	C3	=D2*(A3-A2)*$F$1/365
D3:D12	D3	=D2+C3+B3

 

[AhoyNC]

Dave, I agree more with your figures. I wrongly assumed he made 11 payments of $1,000 per month. I now see that it was 10 months at $1,000 plus $100 interest or the $1,100 you are showing.

 

[footoo]

The IRR based on the dates and repayments in post #6 works out at 19.44%.
However, the method per post #6 is correct (17.90%) and IRR is not really appropriate here - unless you want to present a better picture to the lender.

 

[Stclements1]

Thank you all so much for your guidance, time and efforts, by using the XIRR formula with the data being that the loan was made on the 1st of January 2020 there was no payment in February then on the first of the proceeding ten months with the final payment being the 1st of December 2020 there was a capital payment of 1000 and an interest payment of 100 I got a return of 19.44%. Once again many thanks for all your contributions.

 

[Stclements1]

I have tried the calculation in reply #6 but and I ask that you excuse my ignorance, what I cannot understand is that when you use the value in cell F1 the formula you used to get that number because having gone through it, it is the correct number but I am none the wiser as to the formula to arrive at it. XIRR returns 19.6 which is incorrect and I would like to understand how I can input the formula that will return me the correct number of 17.90