Gallery
About Financial Essentials
Share
Explore

icon picker
All Formulas

Here is a description of every formula in Financial Essentials (bookmark this page for handy reference)

Loans and Annuities

Payment​(rate, periods, presentValue, paymentTiming​​)
Calculates the periodic payment on a loan or annuity
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValue : The principal amount or present value
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
PresentValue​(rate, periods, payment, futureValue, paymentTiming)
Calculates the present value of an annuity and/or a future amount
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
payment : The periodic payment
OPTIONAL INPUTS
futureValue : A future amount after the last period
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
FutureValue​(rate, periods, payment, presentValue, paymentTiming​)
Calculates the future value of an annuity and/or a current amount
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
payment : The periodic payment
OPTIONAL INPUTS
presentValue : A current amount
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
Periods​(rate, payment, presentValue, futureValue, paymentTiming​)
Calculates the number of periods for a loan or annuity
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal. May be zero if a payment is provided.
payment : The periodic payment. May be zero if a rate is provided.
presentValue : A current amount. May be zero if a futureValue is provided.
OPTIONAL INPUTS
futureValue : A future amount after the last period
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
Rate​(periods, payment, presentValue, futureValue, paymentTiming, guess​)
Calculates the interest rate on a loan or the rate of return on an annuity
REQUIRED INPUTS
periods : The number of periods
payment : The periodic payment
presentValue : A current amount
OPTIONAL INPUTS
futureValue : A future amount after the last period
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
guess : Rate guess to help calculation, expressed as a decimal per period. Defaults to 0.1. If rate does not calculate, try different values for guess.

Amortization

CumulativeInterest​(rate, periods, presentValue, startPeriod, endPeriod, paymentTiming​)
Calculates the cumulative interest payments for a given range of periods of an amortized loan
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValue : The principal amount or present value
startPeriod : The first period of the range
endPeriod : The last period of the range
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
CumulativePrincipal​(rate, periods, presentValue, startPeriod, endPeriod, paymentTiming​)
Calculates the cumulative principal payments for a given range of periods of an amortized loan
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValue : The principal amount or present value
startPeriod : The first period of the range
endPeriod : The last period of the range
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
InterestPayment​(rate, periods, presentValue, period, paymentTiming​)
Calculates the interest portion of an amortized loan payment for a given period
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValue : The principal amount or present value
period : The period for which the interest payment is to be calculated
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
PrincipalPayment​(rate, periods, presentValue, period, paymentTiming​)
Calculates the principal portion payment of an amortized loan payment for a given period
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValu e: The principal amount or present value
period : The period for which the principal payment is to be calculated
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
TotalInterest​(rate, periods, presentValue, paymentTiming​)
Calculates the total interest that would be paid on an amortized loan
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValue : The principal amount or present value
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.
PrincipalBalance​(rate, periods, presentValue, period, paymentTiming​)
Calculates the principal balance of an amortized loan as of a given period
REQUIRED INPUTS
rate : The rate per period, expressed as a decimal
periods : The number of periods
presentValue : The principal amount or present value
period : The period for which the principal balance is to be calculated
OPTIONAL INPUTS
paymentTiming : The assumed timing of payments. Valid options are "end" (default) or "beginning" of each period.

Discounted Cash Flow Analysis

NPV​(rate, amounts​)
Calculates the net present value of a series of cash flows
INPUTS
rate : The rate per period, expressed as a decimal
amounts : A list or column of cash flows with one cash flow per period. Use negative values for outflows and positive values for inflows.
IRR​(amounts, guess​)
Calculates the internal rate of return for a series of cash flows
REQUIRED INPUTS
amounts : A list or column of cash flows with one cash flow per period. Use negative values for outflows and positive values for inflows. For valid result, include at least one negative and one positive cash flow.
OPTIONAL INPUTS
guess : IRR guess to help calculation, expressed as a decimal per period. Defaults to 0.1. If IRR does not calculate, try different values for guess.
XIRR​(dates, amounts, guess​)
Calculates the extended internal rate of return for a series of dates and cash flows
REQUIRED INPUTS
dates : A list or column of dates that corresponds to the list of amounts provided in the amounts parameter.
amounts : A list or column of cash flow amounts that corresponds to the list of dates provided in the dates parameter. Use negative values for outflows and positive values for inflows. For valid result, include at least one negative and one positive cash flow.
OPTIONAL INPUTS
guess : XIRR guess to help calculation, expressed as a decimal per period. Defaults to 0.1. If XIRR does not calculate, try different values for guess.

Dates

DaysBetweenDates​(startDate, endDate​)
Calculates the number of days between two dates
INPUTS
startDate : The first date
endDate : The second date
InDateRange​(testDate, firstDate, secondDate​)
Checks if a given date is within a range of dates
INPUTS
testDate : The date to test
firstDate : The first date in the range
secondDate : The second date in the range
ToFiscal​(date, firstMonth, type​)
Returns a fiscal date (as text) given a calendar date
REQUIRED INPUTS
date : A calendar date
firstMonth : The first month of the fiscal year, such as "Mar," "May," or "Oct"
OPTIONAL INPUTS
type : The type of period to return. Valid options are "full" date (default), "year," "quarter," or "month."
InFiscal​(date, firstMonth, fiscalPeriod​)
Checks if a given date is within a fiscal period
REQUIRED INPUTS
date : A calendar date to test
firstMonth : The first month of the fiscal year, such as "Mar," "May," or "Oct"
OPTIONAL INPUTS
fiscalPeriod : The fiscal period to test date against, as text, e.g., "1984" for year (the default), "Q2 1984" for quarter, or "05 1984" for month
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.