Problema: la funzione TIR.COST (Tasso Interno di Rendimento) restituisce un unico valore per l’IRR anche quando ce ne è più di uno
Senza discutere sull’interpretazione dell’IRR stesso, immaginiamo di avere i seguenti due scenari di flusso di cassa.
I Scenario
Questo produce un grafico VAN-IRR come quello seguente:
In questo caso abbiamo due valori dell’IRR rispettivamente 15,47% e 33,33%; la funzione TIR.COST restituisce solamente il primo valore.
II Scenario
A questa serie di flussi di cassa corrisponde il seguente grafico:
In questo caso abbiamo due valori dell’IRR rispettivamente 1,99% e 19,65%; la funzione TIR.COST restituisce solamente il secondo valore.
Costruiamo quindi in VBA le funzioni necessarie per effettuare questo calcolo; come già mostrato anche qui utilizzeremo una funzione che restituisca un vettore di valori sotto forma di Variant, in modo da poterli valutare singolarmente (per esempio utilizzando le funzioni MIN e MAX, quando i valori sono solo due, quindi =MIN(IRRX(A1:A10)) oppure =MAX(IRRX(A1:A10))).
La funzione finale si chiama IRRX, quella che utilizzeremo dentro il foglio excel.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
Public Function IRRX(ByVal intervallo As Range, Optional ByVal max As Double = 300) As Variant Dim n As Integer Dim i As Double Dim irrs, ris As Variant irrs = IRRv1(intervallo, 0.01, max) ReDim ris(1 To 1) n = 0 For k = 1 To UBound(irrs) i = irrs(k) Dim tmp As Variant tmp = IRRv2(intervallo, i - 0.01, i + 0.01) n = n + 1 ReDim Preserve ris(1 To n) ris(n) = tmp Next k IRRX = ris End Function Private Function IRRv1(ByVal intervallo As Range, Optional ByVal delta As Double = 0.01, Optional ByVal max As Double = 300, Optional ByVal start As Double = 0) As Variant Dim irrs As Variant Dim VAN, VAN_prec, tir_prec As Double Dim anno, nirr As Integer ReDim irrs(1 To 1) nirr = 0 For tir = start To max Step delta VAN = 0 anno = 0 For Each c In intervallo VAN = VAN + c.Value / (1 + tir) ^ anno anno = anno + 1 Next c If tir > start And VAN_prec * VAN <= 0 Then nirr = nirr + 1 ReDim Preserve irrs(1 To nirr) irrs(nirr) = (tir + tir_prec) / 2 End If VAN_prec = VAN tir_prec = tir Next tir IRRv1 = irrs End Function Private Function IRRv2(ByVal flusso As Range, ByVal min As Double, ByVal max As Double) As Double For i = 0 To 100 tmin = VAN(flusso, min) tmax = VAN(flusso, max) tavg = VAN(flusso, (max + min) / 2) If tavg = 0 Then Exit For End If If tmin * tavg < 0 Then max = (max + min) / 2 Else min = (max + min) / 2 End If Next i IRRv2 = (max + min) / 2 End Function Private Function VAN(ByVal intervallo As Range, ByVal tir As Double) As Double Dim tmp As Double Dim anno As Integer tmp = 0 anno = 0 For Each c In intervallo tmp = tmp + c.Value / (1 + tir) ^ anno anno = anno + 1 Next c VAN = tmp End Function |
Le altre funzioni servono rispettivamente a:
- IRRv1 – calcolare in modo approssimativo tutti gli IRR possibili su uno specifico flusso di cassa, con valore massimo per l’IRR al 30.000% (espresso come max = 300), inutile dire come questo valore possa essere impostato anche a molto meno per velocizzare i calcoli; l’IRR qui viene calcolato per ogni punto percentuale con scarti del 1%
- IRRv2 – qui calcoliamo, per ogni valore di IRR trovato con la precedente funzione, il valore approssimato mediante iterazione su valori che vanno da IRRv2 ± 1%
- VAN – questa funziona calcola semplicemente il VAN sul dato IRR
Testato su Excel 2016.