Difference between revisions of "Excel to Analytica Mappings/Financial Functions"
m (→ISPMT) |
(Added parameters for all Excel functions) |
||
| Line 1: | Line 1: | ||
| − | = ACCRINT = | + | = ACCRINT(issue,first_interest,settlement,rate,par,frequency'',basis,calc_method'') = |
| − | = ACCRINTM = | + | |
| − | = AMORDEGRC = | + | = ACCRINTM(issue,settlement,rate,par'',basis'') = |
| − | = AMORLINC = | + | |
| − | = COUPDAYBS = | + | = AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate'',basis'') = |
| − | = COUPDAYS = | + | |
| − | = COUPDAYSNC = | + | = AMORLINC(cost,date_purchased,first_period,salvage,period,rate'',basis'') = |
| − | = COUPNCD = | + | |
| − | = COUPNUM = | + | = COUPDAYBS(settlement,maturity,frequency'',basis'') = |
| − | = COUPPCD = | + | |
| + | = COUPDAYS(settlement,maturity,frequency'',basis'') = | ||
| + | |||
| + | = COUPDAYSNC(settlement,maturity,frequency'',basis'') = | ||
| + | |||
| + | = COUPNCD(settlement,maturity,frequency'',basis'') = | ||
| + | |||
| + | = COUPNUM(settlement,maturity,frequency'',basis'') = | ||
| + | |||
| + | = COUPPCD(settlement,maturity,frequency'',basis'') = | ||
| + | |||
= CUMIPMT(rate,nper,pv,start_period,end_period,type) = | = CUMIPMT(rate,nper,pv,start_period,end_period,type) = | ||
| Line 19: | Line 29: | ||
[[CumPrinc]](rate,nper,pv'',start_per,end_per,type'') | [[CumPrinc]](rate,nper,pv'',start_per,end_per,type'') | ||
| − | = DB = | + | = DB(cost,salvage,life,period'',month'') = |
| − | = DDB = | + | |
| − | = DISC = | + | = DDB(cost,salvage,life,period'',factor'') = |
| − | = DOLLARDE = | + | |
| − | = DOLLARFR = | + | = DISC(settlement,maturity,pr,redemption'',basis'') = |
| − | = DURATION = | + | |
| − | = EFFECT = | + | = DOLLARDE(fractional_dollar,fraction) = |
| + | |||
| + | = DOLLARFR(decimal_dollar,fraction) = | ||
| + | |||
| + | = DURATION(settlement, maturity, coupon, yld, frequency'',basis'') = | ||
| + | |||
| + | = EFFECT(nominal_rate,npery) = | ||
| + | |||
= FV(rate,nper,pmt'',pv,type'') = | = FV(rate,nper,pmt'',pv,type'') = | ||
| Line 37: | Line 54: | ||
principal * [[Product]](1+schedule,I) | principal * [[Product]](1+schedule,I) | ||
| − | = INTRATE = | + | = INTRATE(settlement,maturity,coupon,yld,frequency'',basis'') = |
| + | |||
= IPMT(rate,per,nper,pv'',fv,type'') = | = IPMT(rate,per,nper,pv'',fv,type'') = | ||
| Line 54: | Line 72: | ||
-pv*(1-per/nper)*rate | -pv*(1-per/nper)*rate | ||
| − | = MDURATION = | + | = MDURATION(settlement,maturity,coupon,yld,frequency'',basis'') = |
| + | |||
= MIRR(values,finance_rate,reinvest_rate = | = MIRR(values,finance_rate,reinvest_rate = | ||
| Line 73: | Line 92: | ||
where I is the index that the net present value is computed over. The [[Time]] index is often used for this index. | where I is the index that the net present value is computed over. The [[Time]] index is often used for this index. | ||
| − | = ODDFPRICE = | + | = ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency'',basis'') = |
| − | = ODDFYEILD = | + | |
| − | = ODDLPRICE = | + | = ODDFYEILD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency'',basis'') = |
| − | = ODDLYIELD = | + | |
| + | = ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency'',basis'') = | ||
| + | |||
| + | = ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency'',basis'') = | ||
= PMT(rate,nper,pv'',fv,type'') = | = PMT(rate,nper,pv'',fv,type'') = | ||
| Line 88: | Line 110: | ||
[[PPmt]](rate,per,nper,pv'',fv,type'') | [[PPmt]](rate,per,nper,pv'',fv,type'') | ||
| − | = PRICE = | + | = PRICE(settlement,maturity,discount,redemption'',basis'') = |
| − | = PRICEDISC = | + | |
| − | = PRICEMAT = | + | = PRICEDISC(settlement,maturity,discount,redemption'',basis'') = |
| + | |||
| + | = PRICEMAT(settlement,maturity,issue,rate,yld'',basis'') = | ||
= PV(rate,nper,pmt'',fv,type'') = | = PV(rate,nper,pmt'',fv,type'') = | ||
| Line 102: | Line 126: | ||
[[Rate]](nper,pmt,pv'',fv,type,guess'') | [[Rate]](nper,pmt,pv'',fv,type,guess'') | ||
| − | = RECEIVED = | + | = RECEIVED(settlement,maturity,investment,discount'',basis'') = |
| − | = SLN = | + | |
| − | = SYD = | + | = SLN(cost,salvage,life) = |
| − | = TBILLEQ = | + | |
| − | = TBILLPRICE = | + | = SYD(cost,salvage,life,pr) = |
| − | = TBILLYIELD = | + | |
| − | = VDB = | + | = TBILLEQ(settlement,maturity,discount) = |
| + | |||
| + | = TBILLPRICE(settlement,maturity,discount) = | ||
| + | |||
| + | = TBILLYIELD(cost,salvage,life,start_period,end_period'',factor,no_switch'') = | ||
| + | |||
| + | = VDB(cost,salvage,life,start_period,end_period'',factor,no_switch'') = | ||
| + | |||
= XIRR(values,dates'',guess'') = | = XIRR(values,dates'',guess'') = | ||
| Line 121: | Line 152: | ||
where the index I dimensions both ''values'' and ''dates''. | where the index I dimensions both ''values'' and ''dates''. | ||
| − | = YIELD = | + | = YIELD(settlement,maturity,rate,pr,redemption,frequency'',basis'') = |
| − | = YIELDDISC = | + | |
| − | = YIELDMAT = | + | = YIELDDISC(settlement,maturity,pr,redemption'',basis'') = |
| + | |||
| + | = YIELDMAT(settlement,maturity,issue,rate,pr'',basis'') = | ||
Revision as of 21:56, 16 February 2012
ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis,calc_method)
ACCRINTM(issue,settlement,rate,par,basis)
AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)
AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis)
COUPDAYBS(settlement,maturity,frequency,basis)
COUPDAYS(settlement,maturity,frequency,basis)
COUPDAYSNC(settlement,maturity,frequency,basis)
COUPNCD(settlement,maturity,frequency,basis)
COUPNUM(settlement,maturity,frequency,basis)
COUPPCD(settlement,maturity,frequency,basis)
CUMIPMT(rate,nper,pv,start_period,end_period,type)
Analytica equivalent:
CumIPmt(rate, nper, pv,start_per,end_per,type)
CUMPRINC(rate,nper,pv,start_period,end_period,type)
Analytica equivalent:
CumPrinc(rate,nper,pv,start_per,end_per,type)
DB(cost,salvage,life,period,month)
DDB(cost,salvage,life,period,factor)
DISC(settlement,maturity,pr,redemption,basis)
DOLLARDE(fractional_dollar,fraction)
DOLLARFR(decimal_dollar,fraction)
DURATION(settlement, maturity, coupon, yld, frequency,basis)
EFFECT(nominal_rate,npery)
FV(rate,nper,pmt,pv,type)
Analytica equivalent:
Fv(rate,nper,pmt,pv,type)
FVSCHEDULE(principal,schedule)
This computes the future value of principal after compounding interest according to a time-varying array of interest rates. So when this is used, «schedule» is an array. In Analytica, since «schedule» is an array, it will have an index, say I. The equivalent is then:
principal * Product(1+schedule,I)
INTRATE(settlement,maturity,coupon,yld,frequency,basis)
IPMT(rate,per,nper,pv,fv,type)
Analytica equivalent:
IPmt(rate,per,nper,pv,fv,type)
IRR(values,guess)
Analytica equivalent:
Irr(values,I,guess)
where I is the index that the internal rate of return is computed over. This index parameter is often the Time index.
ISPMT(rate,per,nper,pv)
The Analytica equivalent is:
-pv*(1-per/nper)*rate
MDURATION(settlement,maturity,coupon,yld,frequency,basis)
MIRR(values,finance_rate,reinvest_rate
Analytica equivalent (requires Analytica 4.2 or later):
MIrr(values,I,finance_rate,reinvest_rate)
where the array of cash flows, values, is indexed by I.
NOMINAL
NPER(rate,pmt,pv,fv,type)
Analytica equivalent:
NPer(rate,pmt,pv,fv,type)
NPV(rate,value1,value2,...)
Analytica equivalent:
Npv(rate,values,I)
where I is the index that the net present value is computed over. The Time index is often used for this index.
ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency,basis)
ODDFYEILD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency,basis)
ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,basis)
ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)
PMT(rate,nper,pv,fv,type)
Analytica equivalent:
Pmt(rate,nper,pv,fv,type)
PPMT(rate,per,nper,pv,fv,type)
Analytica equivalentt:
PPmt(rate,per,nper,pv,fv,type)
PRICE(settlement,maturity,discount,redemption,basis)
PRICEDISC(settlement,maturity,discount,redemption,basis)
PRICEMAT(settlement,maturity,issue,rate,yld,basis)
PV(rate,nper,pmt,fv,type)
Analytica equivalent:
Pv(rate,nper,pmt,fv,type)
RATE(nper,pmt,pv,fv,type,guess)
Analytica equivalent:
Rate(nper,pmt,pv,fv,type,guess)
RECEIVED(settlement,maturity,investment,discount,basis)
SLN(cost,salvage,life)
SYD(cost,salvage,life,pr)
TBILLEQ(settlement,maturity,discount)
TBILLPRICE(settlement,maturity,discount)
TBILLYIELD(cost,salvage,life,start_period,end_period,factor,no_switch)
VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
XIRR(values,dates,guess)
Analytica equivalent:
XIrr(values,dates,I,guess)
where the index I dimensions both values and dates.
XNPV(rate,values,dates)
Analytica equivalent:
XNpv(rate,values,dates,I)
where the index I dimensions both values and dates.
YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
YIELDDISC(settlement,maturity,pr,redemption,basis)
YIELDMAT(settlement,maturity,issue,rate,pr,basis)
Comments
Enable comment auto-refresher