Financial functions – A to PD
Table of Contents
- How to use the ACCRINT function
- How to use the ACCRINTM function
- How to use the AMORLINC function
- How to use the CUMIPMT function
- How to use the CUMPRINC function
- How to use the DB function
- How to use the DDB function
- How to use the DOLLARFR function
- How to use the EFFECT function
- How to use the FV function
- How to use the IPMT function
- How to use the ISPMT function
- How to use the NOMINAL function
- How to use the NPER function
- How to use the PDURATION function
1. How to use the ACCRINT function
What is the ACCRINT function?
The ACCRINT function calculates the accrued interest for a security that pays periodic interest.
What is a security?
A security that pays interest is a bond that investors use to loan money to a company or a government in exchange for interest payments. The interest rate is fixed and does not change over the life of the security.
What is accrued interest?
In finance, accrued interest is the interest on a bond that has accumulated since the last coupon payment.
What is periodic interest?
The interest payments are usually made periodically, such as every three, six months or twelve months, until the security matures and the principal amount is repaid.
Table of Contents
1. ACCRINT Function Syntax
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
2. ACCRINT Function Arguments
issue | Required. The security's issue date. |
first_interest | Required. The security's first interest date. |
settlement | Required. The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. |
rate | Required. The security's annual coupon rate. |
par | Required. The security's par value. Default value is $1,000. |
frequency | Required. The number of coupon payments per year. |
frequency | payments |
---|---|
1 | Annual |
2 | Semiannual |
4 | Quarterly |
[basis] | Optional. The type of day count basis to use. |
Basis | Day count basis |
---|---|
0 (default value) | US (NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
[calc_method] | Optional. A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. |
calc_method | Function returns |
---|---|
TRUE | Returns the total accrued interest from issue to settlement. |
FALSE | Returns the accrued interest from first_interest to settlement. Default value. |
3. ACCRINT function Example
The image above shows the following details for a bond:
Issue date: March 1, 2018
First interest date: August 31, 2018
Settlement date: May 1, 2018
Coupon rate: 5% (0.05)
Par value: $2,000
Frequency: 4 (quarterly payments)
Basis: 0 (US (NASD) 30/360)
Formula in cell C9:
The ACCRINT function requires Excel dates to work properly, the image above shows Excel dates in cells C2, C3, and C4. The result is 16.9444444444444.
4. ACCRINT function #VALUE! error
The ACCRINT function may return a #VALUE! error if dates are entered as text values, the image above shows the #VALUE! error in cell C2.
Use the DATE function to enter dates (hard coded dates) in the ACCRINT function . For example, DATE(2018,3,1). The DATE function converts the specified year, month, and day to an Excel date (number).
The following formula shows how to use hard coded values in the ACCRINT function:
The ACCRINT function returns the #NUM! error value:
- if the rate is less than 0 (zero).
- if par value is less than 0 (zero).
- if the frequency is not 1,2, or 4.
- basis is less than 0 (zero) or larger than 4.
- issue date is equal or larger than settlement date.
What is hard coded values in a formula?
Hard coded values in Excel are values that are entered directly into a formula instead of using cell references.
5. Get Excel file
Useful resources
2. How to use the ACCRINTM function
What is the ACCRINTM function?
The ACCRINTM function calculates the accrued interest for a security that pays interest at maturity. ACCRINTM stands for accrued interest at maturity.
What is a security?
A security that pays interest is a bond that investors use to loan money to a company or a government in exchange for interest payments. The interest rate is fixed and does not change over the life of the security.
What is accrued interest?
In finance, accrued interest is the interest on a bond that has accumulated since the last coupon payment.
What is the difference between the ACCRINT function and ACCRINTM function?
ACCRINT function calculates the accrued interest paid by the issuer periodically while the ACCRINTM calculates the interest that is paid at maturity.
For example, the arguments show the differences between these two functions:
ACCRINT(issue_date, first_interest, settlement, rate, par, frequency)
ACCRINTM(issue, settlement, rate, par, [basis])
What is the maturity date of a security?
The maturity date is the future date when the financial instrument expires or terminates. For bonds, it's when the principal investment is repaid to the investor.
For example, a 5-year bond issued today in 2023 will have a maturity date of 2028. That's when the original bond investment will be returned after the 5 years is up.
What is the security's issue date?
The issue date of a security is the date that the security was first issued or sold to investors. For bonds, the issue date is the date the bond was first offered and sold by the issuing entity. It establishes when the security's term or maturity period begins. The issue date starts the clock for calculating payments like interest or dividends.
What is the the security's settlement date?
The settlement date of a security is the date that the trade is finalized and the buyer must make payment to complete the transaction.
What is the security's annual coupon rate?
The annual coupon rate is the interest rate that a bond issuer pays annually to bondholders based on the face value of the bond. It is stated as a percentage of the bond's par value. The coupon rate is set when the bond is first issued and generally doesn't change.
What is the security's par value?
The par value, or face value, of a security is the nominal value or dollar amount assigned to the security by the issuer. Par value is the amount the bondholder receives back at maturity.
ACCRINTM Function Syntax
ACCRINTM(issue, settlement, rate, par, [basis])
Arguments
issue | Required. The security's issue date. |
settlement | Required. The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. |
rate | Required. The security's annual coupon rate. |
par | Required. The security's par value. Default value is $1,000. |
[basis] | Optional. The type of day count basis to use. |
Basis | Day count basis |
---|---|
0 (default value) | US (NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
ACCRINTM Function example
Formula in cell C7:
fdhdh
3. How to use the AMORLINC function
What is the AMORLINC function?
The AMORLINC function calculates the depreciation for each accounting period. This function is designed for the French accounting system. The prorated depreciation is taken into account if an asset is bought in the middle of the accounting period.
AMORLINC is an abbreviation for “Amortissement linéaire” which means “linear depreciation” in French.
What is depreciation?
Depreciation is an accounting method that allows businesses to allocate the cost of a tangible asset over its useful life. It represents how much of an asset’s value has been used up over time.
Depreciation helps businesses to match their expenses with their revenues, and to reduce their taxable income by deducting the depreciation expense.
What is an accounting period?
An accounting period is a span of time that covers certain accounting functions such as recording transactions etc. Some common types of periods are: Calendar year, fiscal year, and interim periods.
What is salvage value?
Salvage value is the estimated value of an asset at the end of its useful life. It is also known as scrap value or residual value. It is used to calculate the depreciation expense of an asset over its useful life.
What is prorated depreciation?
Prorated depreciation is a way of calculating the depreciation expense of an asset based on the proportion of time it is used in a given period. Prorated depreciation is often used when an asset is acquired or disposed of in the middle of an accounting period, and the depreciation method depends on the number of years or months of service. Prorated depreciation allows the business to match the expense of the asset with the revenue it generates more accurately.
Excel Function Syntax
AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])
Arguments
cost | Required. |
date_purchased | Required. |
first_period | Required. The date of the end of the first period. |
salvage | Required. The salvage value. |
period | Required. |
rate | Required. The rate of depreciation. |
[basis] | Required. The year count. |
Basis | Day count |
0 (default) | 360 (NASD) |
1 | Actual |
3 | 365 |
4 | European 360 |
The depreciation rate will grow to 50 percent for the period before the last period and grows to 100 percent for the last period.
Comments
The prorated depreciation is taken into account if an asset is purchased in the middle of the accounting period.
Keep in mind to use the DATE function if you enter dates in the function instead of using cell references.
For example,
Formula in cell C6:
4. How to use the CUMIPMT function
What is the CUMIPMT function?
The CUMIPMT function calculates the accumulated interest based on a start and end period on a loan.
What is accumulated interest?
Accumulated interest refers to the total amount of interest earned on an investment or paid on a loan up to a specified date. This process of interest earning interest is called compounding.
What is a loan?
A loan is an amount of money you borrow from an individual or entity. The lender is usually a corporation, financial institution, or government, however it can also be an individual. A lender is an entity or individual that makes funds available for a borrower that can be a person or a business. The borrower agrees to repay the loan amount plus interest over an agreed-upon period of time.
What are the start and end period on a loan?
The start period or start date is the date the loan begins and interest accrual starts. The end period or maturity date is the final due date when the loan must be fully repaid.
The CUMIPMT function lets you calculate the accumulated interest based on a start and end date, however, these dates must not necessarily be the start and end date of the loan.
For example, a loan is to be repaid after 5 years. The CUMIPMT function lets you calculate the accumulated interest between year 3 and 4.
CUMIPMT function Syntax
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMIPMT function Arguments
rate | Required. The interest rate. |
nper | Required. The total number of periods in an annuity.
Examples: |
pv | Required. Present value. |
start_period | Required. The first period. |
end_period | Required. The last period. |
type | Required. When payments are due.
0 - End of period. |
What is an annuity?
An annuity is a series of equal payments made over a defined period of time. The payments are usually made monthly, quarterly, or annually. These payment intervals are the "periods" that nper counts.
For example, if an annuity pays out quarterly for 3 years, there would be:
4 payments per year
3 years
So nper = 4 * 3 = 12 total payment periods
What is interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is a monthly payment?
A monthly payment is a scheduled payment that occurs every month as part of an ongoing loan, mortgage, or other finance agreement.
What is a quarterly payment?
A quarterly payment is a scheduled payment that occurs every quarter as part of an ongoing loan, mortgage, or other finance agreement.
CUMIPMT function example
The image above shows the CUMIPMT function in cell E3 calculating the accumulated interest for month 25 to 36 for a 10 year loan of 100 000.
Formula in cell E3:
The first argument is the interest rate per year, we calculate the interest on a monthly basis so the argument has to be divide with 12, there are 12 months in a year.
The second argument is the number of periods the interest is calculated for, 10 year * 12 = 120 months or periods.
CUMIPMT function not working
The CUMIPMT function returns
- #NUM error value if:
- rate <= 0
- nper <= 0
- pv <= 0
- start_period < 1
- end_period < 1
- start_period > end_period
- type is not 0 (zero) or 1
Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10-year period.
This image shows the interest for months 25 to 36 and the cumulative interest. The amount in cell G22 is the same as in the top image cell E3.
5. How to use the CUMPRINC function
What is the CUMPRINC function?
The CUMPRINC function calculates the accumulated principal based on a start and end period on a loan.
What is accumulated principal?
The original principal is the amount originally borrowed. As payments are made part of each payment goes towards reducing the principal and the remaining part pays the interest.
On a loan, accumulated principal increases over time as payments are made until the loan is repaid.
CUMPRINC function Syntax
CUMPRINC(rate, nper, pv, start_period, end_period, type)
CUMPRINC function Arguments
rate | Required. The interest rate. |
nper | Required. The total number of periods in an annuity.
Examples: |
pv | Required. Present value. |
start_period | Required. The first period. |
end_period | Required. The last period. |
type | Required. When payments are due.
0 - End of period. |
What is an annuity?
An annuity is a series of equal payments made over a defined period of time. The payments are usually made monthly, quarterly, or annually. These payment intervals are the "periods" that nper counts.
For example, if an annuity pays out quarterly for 3 years, there would be:
4 payments per year
3 years
So nper = 4 * 3 = 12 total payment periods
What is interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is a monthly payment?
A monthly payment is a scheduled payment that occurs every month as part of an ongoing loan, mortgage, or other finance agreement.
What is a quarterly payment?
A quarterly payment is a scheduled payment that occurs every quarter as part of an ongoing loan, mortgage, or other finance agreement.
CUMPRINC function example
The image above shows the CUMPRINC function in cell E3 calculating the accumulated principal for month 25 to 36 for a 10 year loan of 100 000.
Formula in cell E3:
Comments
The CUMPRINC function returns
- #NUM error value if:
- rate <=0
- nper <=0
- pv<=0
- start_period < 1
- end_period < 1
- start_period > end_period
- type is not 0 (zero) or 1
Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.
This image shows the principal for month 25 to 36 and the cumulative principal. The amount in cell G22 is the same as in the top image cell E3.
6. How to use the DB function
What is the DB function?
The DB function calculates the depreciation of an asset for a given period using the fixed-declining balance method.
What is depreciation of an asset?
Depreciation is an accounting method that allows businesses to allocate the cost of a tangible asset over its useful life. It represents how much of an asset’s value has been used up over time.
Depreciation helps businesses to match their expenses with their revenues, and to reduce their taxable income by deducting the depreciation expense.
What is fixed-declining balance method?
The fixed-declining balance method is an accelerated depreciation method used to depreciate assets faster than straight-line depreciation.
A fixed rate or percentage is applied each year to the remaining book value, this causes the depreciation amount to decline each year as the book value declines.
Results in higher depreciation early on and lower depreciation later compared to straight-line. Useful for assets expected to lose more value early on in their lifespan.
DB function Syntax
DB(cost, salvage, life, period, [month])
DB function Arguments
Cost | Required. What you pay for the asset. |
Salvage | Required. The value of the asset at the end of depreciation. |
Life | Required. The number of periods the asset is being depreciated. |
Period | Required. The period you want to know the depreciation of. |
[Month] | Optional. The number of months in the first year. |
DB function example
This example demonstrates the depreciation of an asset valued 500,000 across 10 periods ending with a salvage value of 50,000. The example shows that depreciation is larger the initial periods which is shown in the chart below.
Formula in cell F3:
The DB function calculates the decline in value for each period from 1 to 10, the remaining value of the asset is shown in cell range G3:G12. The cost value is specified in cell C3 and the salvage value is specified in cell C4.
Lastly, the number of periods is specified in cell C5. The DB function calculates the depreciation for a given period using these input values.
How is the DB function calculated?
The DB function calculates the depreciation of a period like this:
(cost - total depreciation from prior periods) * rate
where:
rate = 1 - ((salvage / cost) ^ (1 / life))
The first period is calculated like this:
cost * rate * [month] / 12
The last period is calculated like this:
((cost - total depreciation from prior periods) * rate * (12 - [month])) / 12
7. How to use the DDB function
What is the DDB function?
The DDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input.
What is depreciation of an asset?
Depreciation is an accounting method that allows businesses to allocate the cost of a tangible asset over its useful life. It represents how much of an asset’s value has been used up over time.
Depreciation helps businesses to match their expenses with their revenues, and to reduce their taxable income by deducting the depreciation expense.
What is the double-declining balance method?
The double-declining balance method is an accelerated depreciation technique used in accounting and finance, it is a specific application of the declining balance method.
Depreciation is calculated by multiplying the asset's book value by a fixed rate of 200% each year. This results in the asset being fully depreciated over roughly half the straight-line depreciation schedule.
The depreciation amount declines each year as the asset's book value declines, depreciation is highest in the first year, tapering down each subsequent year.
DDB Function Syntax
DDB(cost, salvage, life, period, [factor])
DDB Function Arguments
Cost | Required. What you pay for the asset. |
Salvage | Required. The value of the asset at the end of depreciation. |
Life | Required. The number of periods the asset is being depreciated. |
Period | Required. The period you want to know the depreciation of. |
[factor] | Optional. How quickly the balance declines, default value is 2 (the double-declining balance method). |
DDB Function example
This example demonstrates the depreciation of an asset valued 500,000 across 10 periods ending with a salvage value of 50,000 using the double-declining balance method. The example shows that depreciation is larger for the initial periods.
Formula in cell F3:
The DDB function calculates the decline in value for each period from 1 to 10, the remaining value of the asset is shown in cell range G3:G12. The cost value is specified in cell C3 and the salvage value is specified in cell C4.
Lastly, the number of periods is specified in cell C5. The DDB function calculates the depreciation for a given period using these input values.
The image above shows a chart populated with values from the DDB function using two different factors 2 and 3. Factor 3 shows a significant larger initial decline than a factor of 2.
How is the DDB function calculated?
Here is how the DDB function calculates the depreciation of a period:
Min( (cost - total depreciation from prior periods) * (factor/life), (cost - salvage - total depreciation from prior periods) )
8. How to use the DOLLARFR function
What is the DOLLARFR function?
The DOLLARFR function converts a decimal number to its equivalent in fractional numbers, used in securities denominated in dollars. DOLLARFR is an abbreviation for dollar fraction.
It wont display the entire fraction in the cell, only the numerator. For example, decimal number 1.5 with a the denominator of 16 returns 1.08 which is to be read as 1 and 8/16
Why use fractional numbers instead of decimal numbers in securities denominated in dollars?
The use of fractional numbers instead of decimal numbers in securities denominated in dollars is a historical practice that dates back to the Spanish colonization of the Americas. By using fractions such as 1/8 or 1/16 of a dollar allowed market makers to earn more profits from the bid-ask spread.
The fractional system became obsolete and inefficient with the use of electronic trading and increased competition and was abandoned in 2001.
What is a fractional number?
A fractional number represents a part or portion of a whole number. It contains a fraction, which consists of:
numerator / denominator
Fraction | Desc |
Numerator | the top number in a fraction representing the number of parts. |
Denominator | the bottom number representing the total number of parts the whole was divided into. |
Some examples of fractional numbers:
1/3
3/5
4/11
645/1100
DOLLARFR function Syntax
DOLLARFR(decimal_dollar, fraction)
DOLLARFR function Arguments
decimal_dollar | Required. A decimal number, example 1.25. |
fraction | Required. An integer that represents the denominator of a fraction. The numerator is always 1. numerator/denominator |
What is the difference between a decimal number and an integer?
The main differences between decimal numbers and integers are:
Decimal Numbers
- Contain a decimal point and fractional part
- Can represent fractional or non-whole values
- Include parts of a whole number
- Digits can be after the decimal point
- Examples: 1.5, 3.1415, -2.8
Integers
- No decimal point or fractional part
- Whole numbers only, no decimals
- Includes negative whole numbers
- Examples: -3, 0, 5, 1000
DOLLARFR function example
The formula in cell D3 converts the decimal number 1.2 to a number read as 1 and 16/8.
Formula in cell D3:
The formula in cell D4 converts the decimal number 1.5 to a number read as 1 and 8/16.
DOLLARFR function not working
The DOLLARFR returns
- #NUM error value if fraction < 0 (zero).
- #DIV/0! error value if fraction = 0 (zero).
9. How to use the EFFECT function
What is the effect function?
The EFFECT function calculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
What is the effective annual interest rate?
The effective annual interest rate is the actual annual interest rate earned on an investment after accounting for compounding frequency.
What is the nominal annual interest rate?
The nominal annual interest rate, also known as the stated annual interest rate, is the rate of interest quoted on an investment or loan without accounting for compounding.
It does not consider compounding periods within the year, often stated as "per annum" or "per year". If interest compounds during the year, the effective annual rate will differ from the nominal rate.
What is the number of compounding periods per year?
The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.
Some common compounding periods:
- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year
The number of compounding periods impacts the effective annual interest rate. More frequent compounding results in higher effective rates.
EFFECT function Syntax
EFFECT(nominal_rate, npery)
EFFECT function Arguments
nominal_rate | Required. The nominal interest rate. |
npery | Required. The number of compounding periods per year. |
EFFECT function example
Formula in cell D3:
How is the EFFECT function calculated?
This is how the EFFECT function is calculated:
n - nominal rate
p - number of compounding periods per year.
For example:
Annually: 10% interest compounded annually: Effective rate = 10%
Monthly: 10% interest compounded monthly:
Effective rate = (1 + 0.10/12)12 - 1 = 10.47%
Daily: 10% interest compounded daily: Effective rate = (1 + 0.10/365)^365 - 1 = 10.52%
The effective annual rate will exceed the stated rate when compounding within a year. The effective rate translates the earned interest into the true annual return rate. It is higher than the periodic rate with frequent compounding.
10. How to use the FV function
What is the FV function?
The FV function returns the future value of an investment based on a constant interest rate.
You can use the FV function with either periodic constant payments, or a single lump sum payment.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is a single lump sum payment?
A single lump sum payment refers to making a financial payment in full all at once, instead of in multiple payments over time.
What is the present value?
The present value is the initial amount that also will earn interest.
Related functions
Function | Description |
---|---|
RATE(nper, pmt, pv, [fv], [type]) | Returns the interest rate per period of an annuity |
PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. |
FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |
PDURATION(rate, pv, fv) | Returns the periods needed for an investment to reach a future value. |
FV function Syntax
FV(rate, nper, pmt, [pv], [type])
FV function Arguments
rate | Required. The interest rate you want to use. |
nper | Required. The total number of payments. |
pmt | Required. The payment made each period. |
[pv] | Optional. The present value. |
[type] | Optional. When payments are due. 0 (zero) is the default value. 0 - At the end of the period. 1 - At the beginning of the period. |
- If pmt is omitted the pv argument must be included.
- You must include the pmt argument if the pv argument is omitted. It is then assumed to be 0 (zero).
- It is possible to use both the pv and pmt arguments at the same time.
Why is pv and pmt arguments entered as negative values in the FV function?
In the FV (Future Value) function, the pv and pmt arguments are typically entered as negative values for a specific reason. Cash inflows are represented as positive values, and cash outflows are negative values.
FV function Example 1
Formula in cell B3:
The picture above shows a table that calculates the future value of $1000 with 10% interest rate for 5 years.
FV function Example 2
The FV function calculates the future value if you save $1000 each year with an interest rate of 10% for five years.
11. How to use the IPMT function
What is the IPMT function?
The IPMT function calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
IPMT stands for Interest Payment, reflecting that the function isolates just the interest portion of a periodic payment calculation based on an interest rate, periods, PV, FV, etc.
What is interest payment?
An interest payment is the amount of interest owed or earned on a loan or investment for a specific payment period. The IPMT function calculates the interest portion of a periodic payment on an investment or loan. Interest payments decline over time as the principal is paid off.
What is an investment?
An investment is the purchase of an asset with the expectation of generating income or appreciation in the future. For example, stocks, bonds, real estate, commodities, art etc.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is the principal?
The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.
IPMT function Syntax
IPMT(rate, per, nper, pv, [fv], [type])
IPMT function Arguments
rate | Required. The interest rate. |
per | Required. The period. |
nper | Required. The total number of periods in an annuity.
Examples: |
pv | Required. Present value. |
[fv] | Optional. Future value, default value is 0 (zero). |
[type] | Optional. When payments are due.
0 - End of period, default value. |
IPMT function example
The image above shows the IPMT function in cell E3 calculating the interest for the second month for a 10-year loan of 100 000.
Formula in cell E3:
Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.
How is the IPMT function calculated?
PV = Present Value (loan amount or initial investment)
Rate = Periodic interest rate
Period = Payment period to calculate interest for
nper is the total number of payment periods or payments.
12. How to use the ISPMT function
What is the ISPMT function?
The ISPMT function calculates the interest paid during a specific period of an investment.
Table of Contents
1. Introduction
What is the difference between the ISPMT function and the IPMT function?
The IPMT function lets you also specify a future value [fv] and when the payments are due [type] which is not the case with the ISPMT function.
ISPMT(rate, per, nper, pv)
IPMT(rate, per, nper, pv, [fv], [type])
What is interest payment?
An interest payment is the amount of interest owed or earned on a loan or investment for a specific payment period. The IPMT function calculates the interest portion of a periodic payment on an investment or loan. Interest payments decline over time as the principal is paid off.
What is an investment?
An investment is the purchase of an asset with the expectation of generating income or appreciation in the future. For example, stocks, bonds, real estate, commodities, art etc.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is the principal?
The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.
2. ISPMT Function Syntax
ISPMT(rate, per, nper, pv)
3. ISPMT Function Arguments
rate | Required. The interest rate. 10% = 0.1 |
per | Required. The period for which you want to find the interest, and must be between 1 and nper. |
nper | Required. The total number of payment periods. |
pv | Required. The present value. For a loan, pv is the loan amount. |
Make sure you use the same unit across all arguments.
Negative numbers is cash you pay and positive numbers is cash you receive.
4. ISPMT Function example
Formula in cell B3:
5. How is the ISPMT function calculated?
13. How to use the NOMINAL function
What is the NOMINAL function?
The NOMINAL function calculates the nominal annual interest rate based on the effective rate and the number of compounding periods per year.
What is the difference between the NOMINAL function and the EFFECT function?
NOMINAL function calculates the nominal annual interest rate based on the effective rate without taking compounding into account.
EFFECT function calculates the effective annual interest rate based on the nominal rate factoring in the effect of compounding periods within the year.
Knowing both rates helps properly compare interest costs and earnings on loans and investments.
What is the effective annual interest rate?
The effective annual interest rate is the actual annual interest rate earned on an investment after accounting for compounding frequency.
What is the nominal annual interest rate?
The nominal annual interest rate, also known as the stated annual interest rate, is the rate of interest quoted on an investment or loan without accounting for compounding.
It does not consider compounding periods within the year, often stated as "per annum" or "per year". If interest compounds during the year, the effective annual rate will differ from the nominal rate.
What is the number of compounding periods per year?
The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.
Some common compounding periods:
- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year
The number of compounding periods impacts the effective annual interest rate. More frequent compounding results in higher effective rates.
NOMINAL Function Syntax
NOMINAL(effect_rate, npery)
NOMINAL Function Arguments
effect_rate | Required. The effective interest rate. |
npery | Required. The number of compounding periods per year. |
NOMINAL Function Example
Formula in cell B7:
NOMINAL function not working
The NOMINAL function returns:
- #VALUE! if an argument is not a numerical value.
- #NUM! if effect_rate is smaller than 0 (zero) or if npery is smaller than 1.
The npery argument is automatically converted to a whole number by removing the decimals.
The NOMINAL function is related to the EFFECT function.
14. How to use the NPER function
The NPER function calculates the number of payment periods for a loan or an investment based on periodic constant payments and a fixed interest rate.
What is a payment period?
A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.
What is a loan?
A loan is an amount of money you borrow from an individual or entity. The lender is usually a corporation, financial institution, or government, however it can also be an individual. A lender is an entity or individual that makes funds available for a borrower that can be a person or a business. The borrower agrees to repay the loan amount plus interest over an agreed-upon period of time.
What is an investment?
An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.
What is periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is fixed interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
Table of Contents
1. NPER Function Syntax
NPER(rate, pmt, pv, [fv], [type])
2. NPER Function Arguments
rate | Required. The interest rate per period. |
pmt | Required. The payment per period; it must be constant. |
pv | Required. Argument pv is an abbreviation for present value, in other words, the total value right now based on future payments. |
[fv] | Optional. The future value, or the value you want to achieve when the last payment is made. Default value is 0 (zero). |
[type] | Optional. When payments are due. 0 (zero) - Default value. At the end of the period. 1 - At the beginning of the period. |
3. NPER Function Example
This example shown in the image above demonstrates the NPER function in cell C7. It calculates the number of periods needed to repay $1000 based on a constant payment of $100 and a fixed interest rate of 5%.
Formula in cell F3:
The table below the calculation in the image above shows interest, amortization, and the remaining loan amount after each periodic payment. Get the workbook to see how the calculations are made, link below.
Note, the specified interest is on a yearly payment period, this makes the function return how many years it takes to repay the loan. However, to make it return a monthly payment period divide 5% by 12 (there are 12 months in a year) and you get approx. 0.42% which is the interest rate per month.
The NPER function returns 10.23 months to repay the loan.
4. How is the NPER function calculated?
Use the following math formula to calculate the number of periods :
nper = log((pmt - rate * pv) / (pmt + rate * fv)) / log(1 + rate)
where:
- nper is the number of periods
- pmt is the payment made each period
- rate is the interest rate per period
- pv is the present value of the investment
- fv is the future value of the investment
For example, if you want to know how many months it will take to pay off a loan of $10,000 at 5% annual interest rate with monthly payments of $200, here is how to populate the formula above:
nper = log((200 - 0.05/12 * 10000) / (200 + 0.05/12 * 0)) / log(1 + 0.05/12)
becomes
log((200 - 0.00416666666666667 * 10000) / (200 + 0.00416666666666667 * 0)) / log(1 + 0.00416666666666667)
becomes
log((200 - 41.6666666666667) / (200 + 0)) / log(1.00416666666666667))
becomes
log(0.791666666666665) / log(1.00416666666666667))
becomes
-0.101457640758777/0.00180580086326235
and returns -56.1842907614321
The result is 56.18 months.
5. NPER function not working
The NPER function returns a #VALUE! error if an invalid argument is used, the example above shows a letter as an interest rate.
The #NAME! error is returned if the NPER function is misspelled, the image above shows this error.
The NPER function returns a #NUM! error if it can't calculate the number of payment periods, in other words no matter how many payment periods the loan grows larger month by month.
6. Fixed payments calculator and amortization schedule
The file below contains a fixed payment calculator, you need Excel 365 to use it. It calculates the amortization table dynamically meaning the data spills to cells below instantly when you change the interest rate, payment and loan value.
Formula in cell B24:
Formula in cell C24:
Formula in cell D24:
Formula in cell E24:
Formula in cell F24:
Get Excel file
Useful links
NPER function - Microsoft
Fixed payment calculator
Microsoft Excel Loan Calculator With Amortization Schedule
15. How to use the PDURATION function
What is the PDURATION function?
The PDURATION function calculates how many periods required by an investment to reach a given amount based on a percentage rate. PDURATION stands for price duration.
What is an investment?
An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.
What are periods?
A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.
What is present value?
The present value is the initial amount that will earn interest/dividend.
What is future value?
The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.
Related functions
Function | Description |
---|---|
RATE(nper, pmt, pv, [fv], [type]) | Returns the interest rate per period of an annuity |
PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. |
FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |
PDURATION(rate, pv, fv) | Returns the periods needed for an investment to reach a future value. |
PDURATION Function Syntax
PDURATION(Rate, Pv, Fv)
PDURATION Function Arguments
Rate | Required. Rate is the interest rate per period. |
Pv | Required. Pv is the present amount of the investment. |
Fv | Required. Fv is the desired future amount of the investment. |
PDURATION function example
Formula in cell C7:
PDURATION not working
The PDURATION function returns
- #VALUE error if invalid data types are used.
- #NUM error if argument values are invalid.
Positive values are required as arguments.
How is the PDURATION function calculated?
The following formula calculates the exact same thing as the PDURATION function.
PDURATION = (log(Fv)-log(Pv))/log(1+r)
Fv - Future value
Pv - Present value
r - rate
Formula in cell C5:
The LOG function calculates the logarithm of a number to a specific base.
Function syntax: LOG(number, [base])
More than 1300 Excel formulasExcel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form