Formulas
This section lists formulas used internally by the calculator.
Time Value of Money
|
where: |
|
|
|
|
|
|
|
|
|
C/Y = compounding periods per year |
|
|
P/Y = payment periods per year |
|
|
I/Y = interest rate per year |
|
where: |
|
|
The iteration used to compute i: |
|
|
where: |
|
|
|
|
Gi = 1 + i k
|
where: |
k = 0 for end-of-period payments |
|
|
k = l for beginning-of-period payments |
|
where: |
|
|
where: |
|
|
where: |
|
|
where: |
|
|
where: |
|
|
where: |
|
|
where: |
|
|
where: |
|
Amortization
|
If computing |
|
|
Let: |
|
|
Iterate from: |
|
|
|
|
|
then: |
|
|
|
|
|
|
|
|
where: |
|
|
|
|
Balance, principal, and interest are dependent on the values of PMT, PV, I/ Y, and pmt1 and pmt2.
Cash Flow
|
where: |
|
|
Net present value depends on the values of the initial cash flow (CF0), subsequent cash flows (CFj), frequency of each cash flow (nj), and the specified interest rate (i).
IRR = 100 x i
|
where: |
i satisfies |
Internal rate of return depends on the values of initial cash flow (CF0), subsequent cash flows (CFj).
Bonds 1Source for bond formulas (except duration): Lynch, John J., Jr., and Jan H. Mayle. Standard Securities Calculation Methods. New York: Securities Industry Association, 1986.
|
Price (given yield) with one coupon period or less to redemption: |
|
where: |
PRI = dollar price per $100 par value |
|
|
RV = redemption value of the security per $100 par value (RV except in those cases where call or put features must be considered) |
|
|
R = annual interest rate (as a decimal; CPN_100) |
|
|
M = number of coupon periods per year standard for the particular security involved (set to 1 or 2 Bond worksheet) |
|
|
DSR = number of days from settlement date to redemption date (maturity date, call date, put date, etc.) |
|
|
E = number of days in coupon period in which the settlement date falls |
|
|
Y = annual yield (as a decimal) on investment with security held to redemption (YLD |
|
|
A = number of days from beginning of coupon period to settlement date (accrued days) |
Note: The first term computes present value of the redemption amount, including interest, based on the yield for the invested period. The second term computes the accrued interest agreed to be paid to the seller.
|
Yield (given price) with one coupon period or less to redemption: |
Price (given yield) with more than one coupon period to redemption:
|
where: |
N = |
number of coupons payable between settlement date and redemption date (maturity date, call date, put date, etc.). (If this number contains a fraction, raise it to the next whole number; for example, 2.4 = 3.) |
|
|
DSC = |
number of days from settlement date to next coupon date |
|
|
K = |
summation counter |
Note: The first term computes present value of the redemption amount, not including interest. The second term computes the present values for all future coupon payments. The third term computes the accrued interest agreed to be paid to the seller.
|
Yield (given price) with more than one coupon period to redemption: Yield is found through an iterative search process using the "Price with more than one coupon to redemption" formula. |
|
Accrued interest for securitites with standard coupons or interest at maturity: |
|
where: |
AI = accrued interest |
|
|
PAR= par value (principal amount to be paid at maturity) |
Depreciation
accumulated depreciation
Values for are rounded to the number of decimals you choose to be displayed.
Straight-line depreciation
First year:
Last year or more: DEP = RDV
Sum-of-the-years’-digits depreciation
First year:
Last year or more: DEP = RDV
Declining-balance depreciation
|
where: |
|
First year:
Unless; ; then use
If , use
If computing last year,
Statistics
Note: Formulas apply to both x and y.
Standard deviation with n weighting (sx):
Standard deviation with n-1 weighting (sx):
Mean:
Regressions
Formulas apply to all regression models using transformed data.
Interest Rate Conversions
|
where: |
|
|
where: |
|
Percent Change
|
where: |
OLD = old value |
|
|
NEW = new value |
|
|
%CH = percent change |
|
|
#PD = number of periods |
Profit Margin
Breakeven
|
where: |
PFT = profit |
|
|
P = price |
|
|
FC = fixed cost |
|
|
VC = variable cost |
|
|
Q = quantity |
Days between Dates
With the Date worksheet, you can enter or compute a date within the range January 1, 1980, through December 31, 2079.
Actual/actual day-count method
Note: The method assumes the actual number of days per month and per year.
DBD (days between dates) = number of days II-number of days I
Number of Days I = (Y1 -YB) Q 365
+ (number of days MB to M1)
+ DT1
Number of Days II = (Y2 -YB) Q 365
+ (number of days MB to M2)
+ DT2
|
where: |
M1 = month of first date |
|
|
DT 1 = day of first date |
|
|
Y 1 = year of first date |
|
|
M 2 = month of second date |
|
|
DT 2 = day of second date |
|
|
Y 2 = year of second date |
|
|
MB = base month (January) |
|
|
DB = base day (1) |
|
|
YB = base year (first year after leap year) |
30/360 day-count method2Source for 30/360 day-count method formula Lynch, John J., Jr., and Jan H. Mayle.Standard Securities Calculation Methods. New York: Securities Industry Association, 1986
Note: The method assumes 30 days per month and 360 days per year.
|
where: |
M1 = month of first date |
|
|
DT 1 = day of first date |
|
|
Y 1 = year of first date |
|
|
M 2 = month of second date |
|
|
DT 2 = day of second date |
|
|
Y 2 = year of second date |
Note: If DT1 is 31, change DT1 to 30. If DT2 is 31 and DT1 is 30 or 31, change DT2 to 30; otherwise, leave it at 31.
Note: