How to calculate total interest on 12 month loan with early payments

[KG]

I would like to calculate the total interest earned on a 12 month loan that
gets paid back in 6 equal, monthly payments in months 7-12. Each of the 6
equal, monthly payments will include 1/6 of the return on investment. Here
is a scenario:

Capital Invetsted = $25,000
Return on Investment (20%) = $5,000
Total Repayment = $30,000
6 Equal Payments = $5,000 ($30,000 / 6)

Payment schedule:
Month
1 $0
2 $0
3 $0
4 $0
5 $0
6 $0
7 $5,000
8 $5,000
9 $5,000
10 $5,000
11 $5,000
12 $5,000

 

[Fred Smith]

You want XIRR (it's in the Analysis Toolpak -- if you haven't loaded it,
goto Tools>Addins)

Assume A1:A7 has your seven cash flows (-25 plus your 6 repayments)
Assume B1:B7 has your seven dates (eg, 1/15/05, 7/15/05, 8/15/05, etc)

Result = XIRR(a1:a7,b1:b7)

 

[Rich Carreiro]

I would like to calculate the total interest earned on a 12 month loan that
gets paid back in 6 equal, monthly payments in months 7-12. Each of the 6
equal, monthly payments will include 1/6 of the return on investment. Here
is a scenario:

Capital Invetsted = $25,000
Return on Investment (20%) = $5,000
Total Repayment = $30,000
6 Equal Payments = $5,000 ($30,000 / 6)

The total interest is obviously $5000. You lent $25000 and were
paid back $30000. Or are you asking about what the annualized
rate of return is? If the latter, just use the XIRR() function
of Excel or something similar in another spreadsheet or financial
calculator. Anyhow, for the scenario you specify, the annualized
rate of return is 25.998%.