Suport Excel Cursul 4

download Suport Excel Cursul 4

of 65

description

.

Transcript of Suport Excel Cursul 4

Evaluare instrumente monetareValoare iniial100Dobnd1%Rata de actualizare14%LunaDobndaRascumprareTOTALFactorFluxuri actualizateLuna 11010.98846787480.9884678748Luna 21010.97706873950.9770687395Luna 31010.96580106050.9658010605Luna 41010.95466332170.9546633217Luna 51010.94365402480.9436540248Luna 611201210.9327716884112.8653742949Pre iniial117.70

Tabloul de amortizareValoare100000Pre emisiune8Valoare nominal10Pre rscumprare12Cupon10%k5%6%7%8%9%10%11%12%13%14%Rambursaretran finalAniiNr. oblig.K rambursatCuponPrima rasc.AnuitateK ramas1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T1/(1+k)^T100100000100001000000.9520.9430.9350.9260.9170.9090.9010.8930.8850.877200100000100001000000.9070.8900.8730.8570.8420.8260.8120.7970.7830.769300100000100001000000.8640.8400.8160.7940.7720.7510.7310.7120.6930.675400100000100001000000.8230.7920.7630.7350.7080.6830.6590.6360.6130.592500100000100001000000.7840.7470.7130.6810.6500.6210.5930.5670.5430.519600100000100001000000.7460.7050.6660.6300.5960.5640.5350.5070.4800.456700100000100001000000.7110.6650.6230.5830.5470.5130.4820.4520.4250.400800100000100001000000.6770.6270.5820.5400.5020.4670.4340.4040.3760.351900100000100001000000.6450.5920.5440.5000.4600.4240.3910.3610.3330.3081010000100000100002000013000000.6140.5580.5080.4630.4220.3860.3520.3220.2950.270TOTAL1000010000010000020000220000900000Anuitatile actualizateAnuitate5%6%7%8%9%10%11%12%13%14%10000952494349346925991749091900989298850877210000907089008734857384178264811679727831769510000863883968163793877227513731271186931675010000822779217629735070846830658763556133592110000783574737130680664996209593556745428519410000746270506663630259635645534650664803455610000710766516227583554705132481745234251399610000676862745820540350194665433940393762350610000644659195439500246044241390936063329307513000079809725916608560215549135012145784418573829635067TOTAL An act.150887140608131238122684114866107711101154951398961384530Nr. Obligaiuni10000Pre15.0914.0613.1212.2711.4910.7710.129.518.968.45VN10101010101010101010Pre emisiune8888888888Rata de actualizare5%6%7%8%9%10%11%12%13%14%Pre15.0914.0613.1212.2711.4910.7710.129.518.968.45k8%Anuitate1/(1+k)^TInitial9 ani8 ani7 ani6 ani5 ani4 ani3 ani2 ani1 an1100000.925992592100000.8573857392593100000.79387938857392594100000.735073507938857392595100000.6806680673507938857392596100000.63026302680673507938857392597100000.583558356302680673507938857392598100000.5403540358356302680673507938857392599100000.5002500254035835630268067350793885739259101300000.463260215650327023575854819228847695554103198111454120370Nr. Obligatiuni10000Pre12.2712.2512.2312.2112.1812.1612.1312.1012.0712.04ScadentaInitial9 ani8 ani7 ani6 ani5 ani4 ani3 ani2 ani1 anPre (k=8%)12.2712.2512.2312.2112.1812.1612.1312.1012.0712.04

Randamentul pana la scadentaValoare100000Pre emisiune8Valoare nominal10Pre rscumprare12Cupon10%Rambursaretran finalAniiNr. oblig.K rambursatCuponPrima rasc.AnuitateK ramas100100000100001000002001000001000010000030010000010000100000400100000100001000005001000001000010000060010000010000100000700100000100001000008001000001000010000090010000010000100000101000010000010000200001300000TOTAL1000010000010000020000220000900000Preul obligaiunii n anul 37.00Randament pn la scaden19.84%UnitarActualizat4001000001000010000010.83442569855001000001000010000010.69626624636001000001000010000010.58098244897001000001000010000010.48478668578001000001000010000010.40451846899001000001000010000010.3375406059101000010000010000200001300000133.6614832265TOTAL10000100000100000200002200009000007.00PreRandament162.99%154.15%145.41%136.80%128.33%1110.05%1011.98%914.20%816.77%719.84%

Randamentul curentValoare100000Pre emisiune8Valoare nominal10Pre rscumprare12Cupon10%Rambursaretran finalAniiNr. oblig.K rambursatCuponPrima rasc.AnuitateK ramas100100000100001000002001000001000010000030010000010000100000400100000100001000005001000001000010000060010000010000100000700100000100001000008001000001000010000090010000010000100000101000010000010000200001300000TOTAL1000010000010000020000220000900000Preul obligaiunii n anul 37.00Randament pn la scaden19.84%UnitarActualizat4001000001000010000010.83442569855001000001000010000010.69626624636001000001000010000010.58098244897001000001000010000010.48478668578001000001000010000010.40451846899001000001000010000010.3375406059101000010000010000200001300000133.6614832265TOTAL10000100000100000200002200009000007.00PreR. curentR. pana la scadenta166.25%2.99%156.67%4.15%147.14%5.41%137.69%6.80%128.33%8.33%119.09%10.05%1010.00%11.98%911.11%14.20%812.50%16.77%714.29%19.84%

Randamentul inainte de scadentaValoare nominal10leiCupon10%Scaden30aniRscumprareadup 20 aniPre de rscumprare20AnulNr. obligK rambCuponAnuitateK ramas1010101010101010101010101010101001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-12001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-13001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-14001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-15001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-16001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-17001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-18001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-19001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-110001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-111001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-112001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-113001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-114001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-115001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-116001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-117001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-118001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-119001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-120001110-19-18-17-16-15-14-13-12-11-10-9-8-7-6-5210011101817161514131211109876542200111011.22%11.08%10.94%10.80%10.65%10.50%10.34%10.17%10.00%9.82%9.64%9.44%9.24%9.03%8.80%23001110240011102500111026001110270011102800111029001110301101110

Valoare nominal10leiCupon10%Scaden30aniRscumprareadup 20 aniPre de rscumprare20AnulNr. obligK rambCuponAnuitateK ramas109.99.89.79.69.59.49.39.29.198.98.88.78.61001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-12001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-13001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-14001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-15001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-16001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-17001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-18001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-19001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-110001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-111001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-112001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-113001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-114001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-115001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-116001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-117001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-118001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-119001110-1-1-1-1-1-1-1-1-1-1-1-1-1-1-120001110-19-19-19-19-19-19-19-19-19-19-19-19-19-19-19210011101818181818181818181818181818182200111011.22%11.33%11.44%11.56%11.68%11.80%11.92%12.04%12.17%12.30%12.43%12.56%12.70%12.84%12.98%23001110240011102500111026001110270011102800111029001110301101110

Pretul obligatiuniiValoare nominal10leiCupon10%Scaden30aniRscumprareadup 20 aniPre de rscumprare20RATA DE ACTUALIZAREAnulNr. obligK rambCuponAnuitateK ramas12%11%10%9%8%7%6%10011100.89285714290.90090090090.90909090910.91743119270.92592592590.93457943930.943396226420011100.79719387760.81162243320.8264462810.84167999330.85733882030.87343872830.8899964430011100.71178024780.73119138130.75131480090.77218348010.7938322410.81629787690.83961928340011100.63551807840.65873097410.68301345540.70842521110.73502985280.7628952120.792093663250011100.56742685570.59345132810.62092132310.64993138630.6805831970.71298617950.747258172960011100.50663112120.53464083610.56447393010.59626732690.63016962690.66634222380.704960540470011100.45234921530.48165841090.51315811820.54703424480.58349039530.62274974190.665057113680011100.4038832280.43392649630.46650738020.50186627970.54026888450.58200910460.627412371390011100.3606100250.39092477140.42409761840.46042777950.50024896710.54393374260.5918984635100011100.32197323660.35218447880.38554328940.42241080690.46319348810.50834929210.5583947769110011100.28747610410.31728331420.35049389950.38753285040.42888285930.47509279640.5267875254120011100.25667509290.28584082360.31863081770.35553472510.39711375860.44401195920.4969693636130011100.22917419010.25751425550.28966437970.32617864690.36769792470.41496444790.4688390222140011100.20461981260.23199482480.26333125430.2992464650.34046104140.3878172410.4423009644150011100.18269626130.20900434670.23939204940.27453804130.3152417050.36244601960.4172650607160011100.16312166180.18829220420.21762913580.25186976270.29189046760.33873459780.3936462837170011100.14564434090.16963261640.19784466890.23107317680.27026895140.31657439050.3713644186180011100.13003959010.15282217690.17985878990.21199374020.25024902910.29586391630.3503437911190011100.11610677690.13767763690.16350799080.19448966990.2317120640.2765083330.3305130105200011100.10366676510.12403390710.1486436280.17843088980.21454820740.25841900280.3118047269210011100.09255961170.11174225860.13513057090.1636980640.19865574760.24151308670.2941554027220011100.08264251040.10066870150.12284597360.15018171010.1839405070.22571316520.2775050969230011100.07378795570.09069252390.11167815780.13778138540.17031528430.21094688330.2617972612240011100.06588210330.08170497640.1015255980.12640494080.15769933730.19714661990.2469785483250011100.05882330660.07360808690.09229599820.11596783560.14601790490.18424917750.2329986305260011100.05252080940.06631359180.08390545290.10639250970.13520176380.1721954930.2198100288270011100.04689357980.05974197460.07627768440.0976078070.12518681830.16093036730.2073679517280011100.04186926770.05382159870.06934334950.08954844680.11591372070.15040221240.1956301431290011100.03738327470.04848792680.06303940860.08215453840.10732751920.14056281540.18455673883011011100.36715716270.48051098610.63039408630.82908249741.0931506581.44503828871.915211448.399.1310.0011.0312.2513.7215.51PRETUL INITIAL DE PIATA AL OBLIGATIUNIIValoare nominal10leiCupon10%Scaden30aniRscumprareadup 20 aniPre de rscumprare20AnulNr. obligK rambCuponAnuitateK ramas8%10011100.925925925920011100.85733882030.925925925930011100.7938322410.85733882030.925925925940011100.73502985280.7938322410.85733882030.925925925950011100.6805831970.73502985280.7938322410.85733882030.925925925960011100.63016962690.6805831970.73502985280.7938322410.85733882030.925925925970011100.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.925925925980011100.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.925925925990011100.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.9259259259100011100.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.9259259259110011100.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.9259259259120011100.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.9259259259130011100.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.85733882030.9259259259140011100.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.7938322410.8573388203150011100.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.73502985280.793832241160011100.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.6805831970.7350298528170011100.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.63016962690.680583197180011100.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.58349039530.6301696269190011100.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.54026888450.5834903953200011100.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.50024896710.5402688845210011100.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.46319348810.5002489671220011100.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.42888285930.4631934881230011100.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.39711375860.4288828593240011100.15769933730.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.36769792470.3971137586250011100.14601790490.15769933730.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.34046104140.3676979247260011100.13520176380.14601790490.15769933730.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.3152417050.3404610414270011100.12518681830.13520176380.14601790490.15769933730.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.29189046760.315241705280011100.11591372070.12518681830.13520176380.14601790490.15769933730.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895140.2918904676290011100.10732751920.11591372070.12518681830.13520176380.14601790490.15769933730.17031528430.1839405070.19865574760.21454820740.2317120640.25024902910.27026895143011011101.0931506581.18060271071.27505092751.37705500181.48721940191.6061969541.73469271041.87346812722.02334557742.18521322362.36003028142.5488327042.7527393203An 1An 2An 3An 4An 5An 6An 7An 8An 9An 10An 11An 12An 13Pre12.2512.2312.2112.1912.1612.1312.1112.0712.0412.0011.9611.9211.87Valoare nominal10leiCupon10%Scaden30aniRscumprareadup 20 aniPre de rscumprare20AnulNr. obligK rambCuponAnuitateK ramas30 ani29 ani28 ani27 ani26 ani25 ani24 ani23 ani22 ani21 ani20 ani19 ani18 ani17 ani16 ani100111011111111111111120011101111111111111113001110111111111111111400111011111111111111150011101111111111111116001110111111111111111700111011111111111111180011101111111111111119001110111111111111111100011101111111111111111100111011111111111111112001110111111111111111130011101111111111111111400111011111111111111115001110111111111111111160011101111111111111111170011101111111111111111800111011111111111111190011101111111111111200011101111111111112100111011111111111220011101111111111230011101111111112400111011111111250011101111111260011101111112700111011111280011101111290011101113011011101130 ani29 ani28 ani27 ani26 ani25 ani24 ani23 ani22 ani21 ani20 ani19 ani18 ani17 ani16 ani0.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.92592592590.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.85733882030.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.7938322410.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.73502985280.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.6805831970.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.63016962690.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.58349039530.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.54026888450.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.50024896710.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.46319348810.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.42888285930.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.39711375860.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.36769792470.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.34046104140.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.3152417050.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046760.29189046763.21079514320.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895140.27026895142.97295846590.25024902910.25024902910.25024902910.25024902910.25024902910.25024902910.25024902910.25024902910.25024902910.25024902910.25024902910.25024902912.75273932030.2317120640.2317120640.2317120640.2317120640.2317120640.2317120640.2317120640.2317120640.2317120640.2317120640.2317120642.5488327040.21454820740.21454820740.21454820740.21454820740.21454820740.21454820740.21454820740.21454820740.21454820740.21454820742.36003028140.19865574760.19865574760.19865574760.19865574760.19865574760.19865574760.19865574760.19865574760.19865574762.18521322360.1839405070.1839405070.1839405070.1839405070.1839405070.1839405070.1839405070.1839405072.02334557740.17031528430.17031528430.17031528430.17031528430.17031528430.17031528430.17031528431.87346812720.15769933730.15769933730.15769933730.15769933730.15769933730.15769933731.73469271040.14601790490.14601790490.14601790490.14601790490.14601790491.6061969540.13520176380.13520176380.13520176380.13520176381.48721940190.12518681830.12518681830.12518681831.37705500180.11591372070.11591372071.27505092750.10732751921.18060271071.09315065830 ani29 ani28 ani27 ani26 ani25 ani24 ani23 ani22 ani21 ani20 ani19 ani18 ani17 ani16 aniPre12.251556668612.231681202112.210215698312.187032954112.161995590512.134955237712.105751656712.074211789312.040148732412.00336063111.963629481511.9207198411.874377427211.824327621411.7702738311

Evaluarea aciunilorDividend constantDividend10leiRata actualizare10%Pre100Dividend constantDividend10leiRata actualizare10%Rata de crestere a dividendului1%Pre111