The Excel CUMPRINC function calculates the cumulative payment on the principal of a loan or investment, between two specified periods.
The syntax of the function is:
CUMPRINC( rate, nper, pv, start_period, end_period, type )
Where the arguments are as follows:
rate
|
-
|
The interest rate, per period.
|
nper
|
-
|
The number of periods over which the loan or investment is to be paid.
|
pv
|
-
|
The present value of the loan / investment.
|
start_period
|
-
|
The number of the first period over which the payment of the principal is to be calculated (must be an integer between 1 and nper).
|
end_period
|
-
|
The number of the last period over which the payment of the principal is to be calculated (must be an integer between 1 and nper).
|
type
|
-
|
An integer (equal to 0 or 1) that specifies whether the payment is made at the start or the end of the period:
0 - the payment is made at the end of the period;
1 - the payment is made at the beginning of the period.
|
Cash Flow Convention:
Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the example below.
Excel Cumprinc Function Example
The following spreadsheet shows the Excel Cumprinc function used to calculate the cumulative payment on the principal, during each year of a loan of $50,000 which is to be paid off over 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.
Formulas:
|
A
|
B
|
1
|
Cumulative payment on the principal, during each
year of a loan of $50,000 that is to be paid off
over 5 years, with an interest rate of 5% per year
(payment is made at the end of each month):
|
2
|
Yr1:
|
=CUMPRINC( 5%/12, 60, 50000, 1, 12, 0 )
|
3
|
Yr2:
|
=CUMPRINC( 5%/12, 60, 50000, 13, 24, 0 )
|
4
|
Yr3:
|
=CUMPRINC( 5%/12, 60, 50000, 25, 36, 0 )
|
5
|
Yr4:
|
=CUMPRINC( 5%/12, 60, 50000, 37, 48, 0 )
|
6
|
Yr5:
|
=CUMPRINC( 5%/12, 60, 50000, 49, 60, 0 )
|
|
Results:
|
A
|
B
|
1
|
Cumulative payment on the principal, during each
year of a loan of $50,000 that is to be paid off
over 5 years, with an interest rate of 5% per year
(payment is made at the end of each month):
|
2
|
Yr1:
|
-$9,027.76
|
3
|
Yr2:
|
-$9,489.64
|
4
|
Yr3:
|
-$9,975.15
|
5
|
Yr4:
|
-10,485.50
|
6
|
Yr5:
|
-11,021.95
|
|
Note that in this example:
-
The payments are made monthly, so we have converted the annual interest rate of 5% into a monthly rate (=5%/12), and the number of years into months (=5*12).
-
The calculated payments are negative values, as they represents outgoing payments (for the individual taking out the loan).
Further examples of the Excel Cumprinc function are provided on the Microsoft Office website.
Dostları ilə paylaş: |
