Documentazione/Funzioni di Calc/ISPMT
TDF LibreOffice Document Liberation Project Blog comunitari Weblate Nextcloud Redmine Ask LibreOffice Donazione
Nome funzione:
INTERESSE.RATA
Categoria:
Analisi finanziaria
Riepilogo:
Calcola gli interessi dovuti per un periodo specifico di un investimento o prestito a tasso fisso, assumendo che lo stesso importo di capitale venga rimborsato in ogni periodo. Gli interessi dovuti in un dato periodo dipendono dal saldo residuo in quel momento e diminuiranno nel corso del tempo.
Sintassi:
INTERESSE.RATA(Tasso; Periodo; Periodi totali; Investimento)
Restituisce:
Restituisce un numero reale, che rappresenta il valore degli interessi dovuti nel periodo specificato per l'investimento o il prestito indicato. Il valore restituito è negativo per i pagamenti e positivo per i depositi. Il numero restituito è nella stessa unità di valuta del valore dell'investimento specificato.
Argomenti:
Tasso è un numero reale (espresso come percentuale, ad esempio 2,5%, o come frazione decimale, ad esempio 0,025), oppure un riferimento a una cella contenente tale numero, che rappresenta il tasso di interesse periodico. È comune che i tassi di interesse siano espressi su base annua e, se necessario, è importante convertirli affinché siano compatibili con la durata del periodo di pagamento.
Period is a real number, or a reference to a cell containing that number, which is the specific period for which the interest is calculated.
Total periods is a non-zero real number, or a reference to a cell containing that number, which is the number of payment periods in the term.
Investment is a real number, or a reference to a cell containing that number, which is the present value of the amount borrowed or invested.
- If any argument is non-numeric, then INTERESSE.RATA reports a #VALORE! error.
- If Total periods is equal to 0,0, then INTERESSE.RATA returns a #NUM! error.
Dettagli aggiuntivi:
- INTERESSE.RATA calculates the interest payment for any period using the formula:
- [math]\displaystyle{ \lt span lang="en" dir="ltr" class="mw-content-ltr"\gt \text{ISPMT}~=~Investment ~ \times ~ Rate ~ \times ~ \left( \frac{Period}{Total~periods} ~-~ 1\right)\lt /span\gt }[/math]
- A consequence of using the above formula is that care must be taken with the value passed to INTERESSE.RATA in the Period argument.
- If the repayment schedule requires the interest payment for the first period to be based on the full value of the Investment argument (and thus be equal to [math]\displaystyle{ -Investment \times Rate }[/math]), then the Period argument should be set to 0 for the first period. In other words, for this case the expected values of Period lie in the range
0 ≤ Period ≤ Total periods - 1
. - If a different repayment schedule applies, such that the first capital repayment is made before the interest payment for the first payment is calculated, then the Period argument should be set to 1 for the first period. In other words, for this case the expected values of Period lie in the range
1 ≤ Period ≤ Total periods
.
- If the repayment schedule requires the interest payment for the first period to be based on the full value of the Investment argument (and thus be equal to [math]\displaystyle{ -Investment \times Rate }[/math]), then the Period argument should be set to 0 for the first period. In other words, for this case the expected values of Period lie in the range
- For an investment of this type, the constant periodic principal payment is [math]\displaystyle{ \frac {Investment}{Total~periods} }[/math] currency units.
- Note that INTERESSE.RATA assumes constant periodic principal amounts but variable periodic interest amounts. This is different to the INTERESSI function, where the total amount due each period (interest plus capital) is constant.
Esempi:
Worked example for complete repayment schedule
John decides to borrow $8 000 over a period of 12 months at an annual interest rate of 4%. There is no cash balance remaining at the end of the loan. The loan is structured so that monthly interest payments are calculated based on the capital balance outstanding at the end of the previous month. The following table shows the schedule for his repayments.
A | B | C | D | |
---|---|---|---|---|
1 | Loan amount | $8 000 | ||
2 | Annual rate | 4% | ||
3 | ||||
4 | Month | Previous Balance |
Principal Payment |
INTERESSE.RATA |
5 | 1 | $8 000,00 | -$666,67 | -26,67 |
6 | 2 | $7 333,33 | -$666,67 | -24,44 |
7 | 3 | $6 666,67 | -$666,67 | -22,22 |
8 | 4 | $6 000,00 | -$666,67 | -20,00 |
9 | 5 | $5 333,33 | -$666,67 | -17,78 |
10 | 6 | $4 666,67 | -$666,67 | -15,56 |
11 | 7 | $4 000,00 | -$666,67 | -13,33 |
12 | 8 | $3 333,33 | -$666,67 | -11,11 |
13 | 9 | $2 666,67 | -$666,67 | -8,89 |
14 | 10 | $2 000,00 | -$666,67 | -6,67 |
15 | 11 | $1 333,33 | -$666,67 | -4,44 |
16 | 12 | $666,67 | -$666,67 | -2,22 |
17 | ||||
18 | SOMMA | -$8 000,00 | -173,33 |
Cells B5:D16 contain formulas as follows:
- Cell B5 contains the formula
=$B$1
. - Cell B6 contains the formula
=$B5+$C6
; cell B7 contains the formula=$B6+$C7
; and so on for cells B8:B16. - Cells C5:C16 all contain the formula
=-$B$1/$A$16
. - Cell D5 contains the formula
=INTERESSE.RATA($B$2/12; $A5-1; $A$16; $B$1)
; cell D6 contains the formula=INTERESSE.RATA($B$2/12; $A6-1; $A$16; $B$1)
; and so on for cells D7:D16.
Significant features of the data in the above table include:
- In all calls to the INTERESSE.RATA function, the first argument is
$B$2/12
. The division by 12 converts the annual interest rate to a monthly equivalent to match the period length. - In all calls to the INTERESSE.RATA function, the second argument is passed as the actual period number minus 1. This approach ensures that the interest in the first (and subsequent) periods is based on the capital balance at the end of the previous period.
- Note that the inclusion of the dollar ($) prefix in cell B1 leads to the values in cells B5:C16 also being displayed as currency values. However, the outputs from the INTERESSE.RATA function displayed in cells D5:D16 are not displayed as currency values by default.
- The value −173,33 in cell D18 is calculated as the sum of the values in cells D5:D16 and therefore represents the total interest paid during the loan term.
Other examples
Formula | Descrizione | Restituisce |
---|---|---|
=INTERESSE.RATA(0,01; 1; 24; 120000) |
Calculate the interest due in the first month for a loan of 120 000 currency units with a two-year term, monthly repayment periods, constant capital repayments each month, and an annual interest rate of 12%. For this example, it is assumed that the first month's interest payment is calculated after the first capital payment ($5 000) has been made. The function returns a value of −1 150 currency units. |
-1150 |
=INTERESSE.RATA(0,01; 18; 24; 120000) |
Calculate the interest due in the eighteenth month for a loan of 120 000 currency units with a two-year term, monthly repayment periods, constant capital repayments each month, and an annual interest rate of 12%. For this example, it is assumed that a month's interest payment is calculated after the capital payment for that month ($5 000) has been made. The function returns a value of −300 currency units. |
-300 |
=INTERESSE.RATA(0,01; 24; 24; 120000) |
Calculate the interest due in the final month for a loan of 120 000 currency units with a two-year term, monthly repayment periods, constant capital repayments each month, and an annual interest rate of 12%. For this example, it is assumed that a month's interest payment is calculated after the capital payment for that month ($5 000) has been made. The function returns a value of 0 currency units, which is expected because the capital repayment in this final month reduces the balance to 0. |
0 |
Funzioni di LibreOffice correlate:
Standard ODF:
Funzioni equivalenti (o simili) di Excel:
ISPMT