| Page: 1 |
1) Beta Probability Density Calculations with Microsoft Excel
Author:
Stephen Nelson
Category:
Software
Microsoft Excel supplies two useful statistical functions for making beta probability calculations, BETADIST and BETAINV. Perhaps surprisingly, neither function is difficult to use as long as understand the inputs that supply data to the function.Understanding the beta probability density function, BETADISTThe BETADIST function returns the cumulative beta probability density function.Statisticians often use the cumulative beta probability density function to study...
|
2) Binomial Probability Distributions with Microsoft Excel
Author:
Stephen Nelson
Category:
Software
A binomial distribution describes the outcome of a multi-step experiment, consisting of
n identical trials, where each trial ends in either a success or a failure and the probability of
a success p does not change from trial to trial. This useful statistical analysis can be performed relatively easily using Microsoft Excel using the Excel BINOMDIST, CRITBINOM and NEGBINOMDIST functions.Note, however, that when making binomial probability calculations, the trials must also be indepen...
|
3) Using Excel for Statistical Analysis - Terms You Should Understand Before You Start
Author:
Stephen Nelson
Category:
Software
Excel provides an almost countless number of statistical tools you can use to analyze data
and make meaningful statements about it. However, without understanding the definitions of the statistical terms that Excel uses, the statistical tools offer little help.Accordingly, this article supplies some background information about Excel's statistical tools and also defines the important statistical terms used by Excel.Distinguishing between types of dataThe science...
|
4) Correlation Measurements with Microsoft Excel
Author:
Stephen Nelson
Category:
Software
Excel provides useful statistical functions for measuring correlation between two variables.As a reminder, the benefit of using a correlation coefficient to measure the relationship between two variables as opposed to using covariance is that the unit of measurement doesn't matter.But a caution: Remember that correlation does not show causation. That is, you could easily show that as the number of ice cream cones consumed increases during a year, so does the number of drownings. But t...
|
5) Using Excel's XIRR and XNPV Add-In Functions
Author:
Stephen Nelson
Category:
Finance
Excel provides standard functions, IRR and NPV, for calculating the internal rate of return and net present value of a set of cash flows. While most often you'll want to use these two functions, they may sometimes present a practical problem: Both the IRR and NPV functions assume you've first constructed a worksheet that arranges the cash flows into equal periods. In other words, to use the IRR or NPV function, you must first construct a worksheet that shows the investment's monthly cash flo...
|
6) Using the French Depreciation Add-In Functions
Author:
Stephen Nelson
Category:
Finance
Excel supplies two special functions for making French depreciation calculations,
AMORDEGRC and AMORLINC.Both functions use a similar set of arguments, including the asset cost, purchase date, date at the end of the first period, salvage value, accounting period, depreciation rate, and basis. You specify the basis as 0 for the US (or NASD) version of 30 days in a month and 360 days in a year; as 1 for the actual number of days in the month and year; 2 for the actual number of days in...
|
7) Using the Yield Add-In Functions
Author:
Stephen Nelson
Category:
Finance
Excel provides five functions that let you make price calculations for securities such as bonds more easily: YIELD, YIELDDISC, ODDFYIELD, ODDLYIELD, and ODDLYIELD. (Excel's online help file supplies the actual formulas used for many of these yield functions.)Some Background Info on the Yield FunctionsAs a group, the functions use a set of standard arguments:The settlement date specifies the date the bond is settled, or purchased.The maturity date specifies...
|
8) Using the Interest Rate Add-In Functions
Author:
Stephen Nelson
Category:
Business
Excel provides four add-in functions that let you make sophisticated interest rate calculations: DISC, EFFECT, INTRATE, and NOMINAL.Some Background Info on the Interest Rate Add-in FunctionsThe DISC and INTRATE functions, which are related, work from the same basic set of
arguments: the settlement date, the maturity date, the redemption value, the price, the frequency,
and the basis.The settlement date specifies the date the bond is settled, or purchased. The ma...
|
9) Calculating Present Values with Microsoft Excel
Author:
Stephen Nelson
Category:
Software
The PV function calculates the present value of an annuity, or future value, given the periodic
rate, number of periods, payment, future value (or balloon payment), and, optionally,
the type-of-annuity switch.Sometimes, why you would use this function seems strange. But it's often useful when working with loans or with investments. For example, if you want to know the current day value of a stream of future payments (such an annuity), you can use the PV function. Or if you want to e...
|
10) Calculating Interest Rates with Microsoft Excel
Author:
Stephen Nelson
Category:
Software
The Rate function calculates the interest rate implicit in a set of loan or investment terms
given the number of periods (months, quarters, years or whatever), the payment per period, the present value, the future value, and, optionally, the type-of-annuity switch, and also optionally, an interest-rate guess.If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning
of the period, following the annuity due convention. If you set the annuity switch to 0...
|
11) Using Microsoft Excel's Accrued Interest Add-In Functions
Author:
Stephen Nelson
Category:
Software
Excel provides two functions that help you with accrued interest calculations for securities
that pay interest. ACCRINT calculates accrued interest for a security (such as a bond) that
pays periodic interest. ACCRINTM calculates accrued interest for a security (such as a zerocoupon
bond) that pays interest upon maturity.Some Background Info on the Accrued Interest FunctionsThe accrued interest functions use a similar set of arguments, including the issue date, first...
|
12) Using the Bond Duration Add-In Functions
Author:
Stephen Nelson
Category:
Software
Excel provides two functions that let you make bond duration calculations: DURATION and MDURATION. Duration, a weighted average measure of the present value of a bond's cash flows, quantifies how a change in the bond yield affects the bond price.Understanding the Bond Duration Function ArgumentsBoth duration functions use the same set of six arguments: the settlement date, the maturity
date, the coupon rate, the yield, the coupon frequency, and the day count basis.
The...
|
| Page: 1 |