How to calculate the interest rate of an investment with financing?

[fabiospark]

Think of a series of events like this:
- event 1: out cash flow to buy a 240k€ photovoltaic plant with my money (liquid) [event 1 = -240000€ | cell C1]
- event 2 to 21, one on each subsequent year: the PV plant gives me back 35k€ a year [event 2 =+35000€ | cell C2],........ [event n =+35000€ | cell C21]
- event 22: I spend 20k€ to get the PV plant dismantled [event 22 =-20000 | cell C22]

If I want to know the interest paid back by this investment I use the function TIR.COST(C1:C22,0.1) and I get something around 13%.

Now let's say I don't want to use my money to buy the PV so I get a loan from the bank at the interest rate of 5% for 20 years.
How can I calculate the interest paid back by the PV, considering also the financial costs of the loan, taking into account that yearly I will have
to pay back the bank with 18.000€?
Not being an expert, this is what I thought to do:
- event 1 (C1) = zero This is because the bank is paying the PV [C1 = 0]
- event 2 to 21, one on each subsequent year: the PV plant gives me back 35k€ a year and I have to give the bank 18k€ so [event 2 =+35000-18000=17000€ | cell C2],........ [event n =+17000€ | cell C21]
- event 22: I spend 20k€ to get the PV plant dismantled [event 22 =-20000 | cell C22]

But if I use the same TIR.COST function as before [TIR.COST(C1:C22,0.1)] I get something around 0.00000048%: what am I doing wrong?

Thanks.
fabio

 

[BSALV]

play a little bit with the green cell, your loan of the bank.
suppose you start with 10.000€ instead of 240.000€ , your PV with help of the bank 'll have an IR of 13.6%.
With a loan of 100.000 already 18.5%, with 200.000€ 47% and then it goes quick passing 100% to 1000% (1600% at 239.000)
So at 240.000€ is is too big to calculate, meaning you won the lotto.

Map1
	A	B	C	D
1		my own PV	loan	combined
2	year		-19178
3	0	-240000	239000	-1000
4	1	35000	-19178	15822
5	2	35000	-19178	15822
6	3	35000	-19178	15822
7	4	35000	-19178	15822
8	5	35000	-19178	15822
9	6	35000	-19178	15822
10	7	35000	-19178	15822
11	8	35000	-19178	15822
12	9	35000	-19178	15822
13	10	35000	-19178	15822
14	11	35000	-19178	15822
15	12	35000	-19178	15822
16	13	35000	-19178	15822
17	14	35000	-19178	15822
18	15	35000	-19178	15822
19	16	35000	-19178	15822
20	17	35000	-19178	15822
21	18	35000	-19178	15822
22	19	35000	-19178	15822
23	20	35000	-19178	15822
24	21	-20000		-20000
25
26		13,30%	5,00%	1582,20%
Blad1

Cell Formulas
Range	Formula
C2	C2	=PMT(0.05,20,C3)
A3:A24	A3	=SEQUENCE(22,,0)
C4:C23	C4	=+$C$2
D3:D24	D3	=+C3+B3
B26:C26	B26	=IRR(B3:B24,0.11)
D26	D26	=IRR(D3:D24,0.07)
Dynamic array formulas.