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?

 

[StephenCrump]

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

Dave has used GoalSeek, as he says in Post #7. You set cell D12 to value 0, by varying cell F1.

Dave's 17.90% and XIRR's 19.44% can be regarded as equivalent. It's not a question of one being correct, and one being wrong. They just use two different conventions to calculate the disclosed interest rate.

It's easier to see if we assume every month has equal duration 365/12 days. Apply IRR() to the cashflows, and you get 1.4912% per month. Under this scenario, Dave's approach and XIRR would both use the same effective rate of 1.4912% per month. However, Dave would disclose an annual interest rate of 12*1.4912% = 17.89%, and XIRR (1+1.4912%)^12 - 1 = 19.44%.

 

[footoo]

Dave's 17.90% and XIRR's 19.44% can be regarded as equivalent. It's not a question of one being correct, and one being wrong. They just use two different conventions to calculate the disclosed interest rate.

But based upon the OP's original post, I think Dave's 17.90% compound interest is the appropriate rate.
A question of horses for courses.

 

[Dave Patton]

Please read the information on #6.
"I built a table and used Goal Seek. "
Put an estimated rate in F1.
Then use Goal Seek to determine what rate will yield 0 in cell D12.

 

[footoo]

Put an estimated rate in F1.

Which could be obtained with the RATE function : =RATE(10,1100,-B1)*10

 

[Dave Patton]

Which could be obtained with the RATE function : =RATE(10,1100,-B1)*10

Does this reflect that the term is 11 months and there is interest only in the first month?

 

[footoo]

Which could be obtained with the RATE function : =RATE(10,1100,-B1)*10

Does this reflect that the term is 11 months and there is interest only in the first month?

Sort of - it is based upon 10 repayments of 1,100 but cannot, of course, handle the missing first month.
Should be close enough to provide an estimate for goal seek purposes.

 

[dcardno]

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.

I'm not sure why you would think IRR (or XIRR) is not appropriate for this analysis - could you elaborate?

 

[footoo]

I'm not sure why you would think IRR (or XIRR) is not appropriate for this analysis - could you elaborate?

Plenty of articles on this subject available on the internet.
For example : CAGR vs. IRR: What's the Difference? and irr vs compound interest - Google Search

 

[dcardno]

Thanks - I know the difference; I just don't know why you would say that IRR isn't appropriate.

 

[footoo]

Thanks - I know the difference; I just don't know why you would say that IRR isn't appropriate.

The OP's scenario is similar to a home mortgage loan subject to fixed repayment amounts.
In such cases it is normal to look at compound interest rates rather than IRR.