Финансови функции на Microsoft Excel за бизнес задачи

Масиви и формули за работа с тях

Масивите са структури от данни, които съдържат фиксиран брой елементи. В програмирането обикновено за тях се поставя ограничението, елементите да бъдат от един и същи тип. В Microsoft Excel то е отслабено. Елементите могат да бъдат от числов, текстов и/или от логически  тип или константи за грешки при условие, че формулите и операциите, прилагани върху масивите допускат това.
В Excel масивите могат да бъдат само едномерни и двумерни (вектори и матрици). Едномерни са тези, при които елементите им са подредени в един ред или колона, а двумерни, когато образуват повече редове и колони. Максималният брой на елементите на масивите не трябва да надхвърля 6500. Елементите на масивите притежават индекси, по които става обръщението към тях. Индексите не се съхраняват в паметта като отделни числа. Местоположението (подреждането) на елементите на масивите в паметта определя еднозначно техните индекси.
Масивите се използват тогава, когато възниква необходимост от групово прилагане на операции над повече данни едновременно. В тези случаи е целесъобразно данните да бъдат първоначално структурирани в масиви, а операциите над тях да бъдат с формули за масиви. В противен случай се налага една и съща операция да се записва многократно за всеки елемент поотделно, което отнема време и заема повече оперативна памет. Така например с матрица с размери 20х20, за да се образуват сумите по редове, вместо 20 единични формули, може да се употреби само една формула за масив.

Един масив може да се опише по два начина – като списък от константи или като референция към област от клетки, съдържанията на които образуват масив. В първия случай масивите се наричат масиви-константи (константни масиви), а във втория масиви-области.
За да може Excel да различава формулите за масиви от обикновените формули, прието е те да бъдат обграждани в големи скоби.
Големите скоби не се въвеждат от клавиатурата като символи. Когато формулата се набира, тя се въвежда без скоби, по традиционния начин, като накрая се подава клавиатурната комбинация <Ctrl+Shift+Enter>, която придизвиква ограждането на формулата със скоби.
Например формулата: {=A1:A3+B1:B3} извършва сумиране на едноименните (с еднакви индекси) елементи на два едномерни масива-области, които са разположени в таблицата под формата на клони. Масивите са описани чрез референциите А1:А3 и В1:В3.
Константните масиви се представят като списък от константни стойности, обграждани в големи скоби.

В качество на разделител между елементите от един и същ ред се иползва запетая (,), а за разделител между редовете точка-запетая (;). Елементите от числен тип могат да се записват и в експоненциална форма.

Примери:

{10,20,30} – едномерен масив-константа с размери 1х3, разположен в ред;
{10;20;30} – едномерен масив-константа с размери 3х1, разположен в колона;
{2.4,4,5.1; „уеб дизайн“, „SEO“, „Интернет“ } – двумерен масив за просто разпределяне на услуги на уеб агенция с размери 2х3, в който елементите са от различен тип – числен и текстов (текстовите константи се записват винаги обградени в двойни кавички).
Константните масиви участват във формулите под формата на аргументи.

Формули за масиви

Както по горе бе отбелязано, формулите за масиви се различават от обикновенните формули по това, че са оградени в големи скоби. Например:
{=С8:С10-В8:В10}
Формулата е предназначена за изваждане на едноименните елементи на два едномерни масива-области, които са разположени под формата на колони. Масивите са описани чрез техните референции С8:С10 и В8:В10. Резултатът от изчислението е масив със същата размерност като тази на изходните масиви.
Резултатът от изчислението на една формула за масив може да бъде единична стойност или друг масив. Например след пресмятане на формулата:
{=SUM(C8:C10-B8:B10)}
ще бъде получена една единственна стойност, представляваща сума от елементите на междинния масив, получен след изваждане на елементите на двата първоначални масива.
Процедурата за въвеждане на формули за масиви е следнта:
1.    Маркира се една клетка или област от таблицата, предназначени за приемане на резултата от пресмятането на формулата. Размерите на областта трябва да съответстват на размерите на получавания резултат. Областта не може да съдържа повече от 1636 клетки.
2.    Набира се формулата по познатия начин без въвеждане на големите скоби.
3.    Подава се <Ctrl+Shift+Enter>, при което Excel обгражда формулата със скоби и попълва маркираната клетка или област с пресметнатия резултат.

Финансов анализ

Финансовите функции в MS Excel дават възможност да се извършват както основни финансови изчисления (оценяване на инвестиции, изчисляване на възвращаемост, амортизационни отчисления, анализ на ценни книжа) без построяването на дълги и сложни формули, така и да се решават разнообразни специализирани задачи от финансовия анализ.
Финансовите функции за анюитетни плащания се основават на постоянни (периодични) плащания и постоянен размер на лихвата. В тях се използват следните основни понятия и означения:

• pv – настояща стойност или сумата, която ще се изплаща (заема) чрез серия бъдещи постоянни вноски;
• fv – бъдеща стойност или сумата, която желаем да имаме след последното периодично плащане. Ако липсва, се приема че тя е нула (напр. бъдещата стойност на заема е нула);
• npv – нетна сегашна (настояща) стойност на серия (редица) от бъдещи давания (отрицателни стойности) и бъдещи вземания (положителни стойности);
• rate – лихвен процент (лихвена ставка) за перода на един анюитет;
• irr – лихва на възвращаемост (изисквана, очаквана норма на възвращаемост) на база  на периодични приходи от нея;
• nper – брой периоди, през които ще се прави плащането;
• pmt – размер на периодичното плащане  (вноската);
• type – падеж, който описва кога в рамките на периода се извършва плащането; 0 или липсва – плащането е в края на периода; 1 – плащането е в началото на периода;

Особености на този тип функции:

1. Ако плащанията на вноските са месечни, то rate се задава като годишната лихва се дели на 12;
2. Ако периодът на плащането е месец, то nper се задава, като срока за плащане в години се умножава по 12;
3. Суми, които се влагат (депозити, вноски) се задават като отрицателни стойности;
4. Суми, които се очакват (дивиденти, заеми) се задават като положителни стойности.

Функции за оценяване на инвестиции

PV (rate;nper;pmt;fv;type) – връща настояща стойност на инвестиция (поредица плащания или единично плащане).
Настоящата стойност е един от най-често използваните показатели за привлекателността на дългосрочна инвестиция. Най-просто казано, тя представлява стойността на инвестицията днес. Определя се чрез дисконтиране на плащанията, които ще се получават, до настоящия момент. Ако настоящата стойност на плащанията е по-голяма от цената на инвестицията, последната е изгодна.

NPV (rate,value1,value2,…value29) – изчислява нетна сегашна стойност.
Нетната сегашна стойност е друг често използван показател за оценяване на изгодата от инвестиция. По принцип всяка инвестиция с по-голяма от нула нетна сегашна стойност се счита за изгодна.
Разрешени са до 29 аргумента за суми на плащания (очаквани постъпления или приходи), като потребителят може да зададе произволен брой аргументи, използвайки масив.
NPV се различава от PV по две важни характеристики. Докато PV работи с постоянна стойност за плащанията, при NPV са позволени различни като суми плащания. Другата важна разлика е, че PV позволява плащанията да стават в началото или края на период, докато при NPV се приема, че всички плащания са равномерно разпределени и стават в края на период. Ако цялата цена на инвестицията трябва да бъде платена предварително, тя не трябва да се включва като аргумент приход, а да се извади от резултата на  функцията. От друга страна, ако трябва да се плати в края на първия период, може да се включи цената като първи, отрицателен аргумент приход.

FV (rate;nper; pmt; pv; type) – изчислява бъдеща стойност.
Бъдеща стойност по същество е противоположното на настояща стойност и функцията FV изчислява стойността в бъдещ момент на инвестиция, приходите от която се получават наведнъж или на периодични еднакви плащания.
Аргументът плащане се използва при изчисляване със серия от плащания, а настояща стойност – с единично плащане.

PMT (rate;nper;pv;fv;type) – връща стойността на периодичните вноски, които трябва да се правят, за да може при известни начална вноска, брой периоди за внасяне и лихвен процент да се осигурят бъдещи постъпления (напр. лизинг).

IPMT (rate;per;nper;pv;fv;type) – изчислява каква част от периодичната вноска е за погасяване на лихвата при изплащане на дадена сума за даден период, базирано на постоянно плащане и постоянен лихвен процент.

PPMT (rate;per;nper;pv;fv;type) – изчислява каква част от периодичната вноска е за погасяване на основната компонента (главницата) при изплащане на дадена сума за даден период, базирано на постоянно плащане и постоянен лихвен процент.

NPER (rate;pmt;pv;fv;type) – пресмята броя периоди, необходими за погасяване на дълг при зададено периодично плащане.

Функции за изчисляване на възвращаемост

Функциите RATE, IRR и MIRR служат за пресмятане на възвръщаемостта от периодично изплащаните суми от инвестиция.

RATE (nper;pmt;pv;fv;type)  – намира възвръщаемостта на инвестиция, която генерира поредица от еднакви периодични плащания или единично плащане.
Плащане (pmt) се използва в случай на поредица еднакви плащания, а крайна стойност (pv) – при единично плащане. Аргументът type, който е незадължителен, дава на Ехеl отправна стойност за получаване на резултата. Ако се пропусне, Excel започва с предположение 0,1 (10%).

IRR (values, guess) – изчислява изисквана норма на възвращаемост на направена инвестиция на базата на периодични приходи от нея.
Вътрешната възвръщаемост на инвестиция е тази, която нулира нетната настояща стойност. С други думи тя кара настоящата стойност на приходите да се изравни точно с цената на инвестицията.
Както и чистата настояща стойност, вътрешната възвръщаемост се използва за сравняване на инвестиции. Изгодна инвестиция е тази, чиято нетна настояща стойност, дисконтирана с подходящата прагова лихва, е по-голяма от нула. Ако се обърне уравнението, ще се получи, че лихвата, която води до получаване на нулева нетна настояща стойност, трябва да е по-голяма от праговата лихва. Така привлекателна инвестиция е тази, за която дисконтовият процент, който нулира нетната настояща стойност – т.е. вътрешната възвръщаемост – е по-голям от праговата лихва.
Функцията IRR е тясно свързана с RATE. Разликите между тях са подобни на разликите между функциите PV и NPV. Както и NPV, IRR може да работи с цена на инвестицията и различни по сума плащания.
Аргументът стойности – values е масив или обръщение към област от клетки, съдържащи числа. Разрешен е само един такъв аргумент и той трябва да включва поне една положителна и една отрицателна стойност. IRR игнорира текста, логическите стойности и празните клетки, предполага, че транзакциите стават в края на периодите и връща еквивалентния за дължината на периода коефициент.
Както и с RATE, аргументът предположение – guess дава на Excel отправна точка за изчисленията и не е задължителен. Ако се получи стой-ностга за грешка #NUM!, когато се въведе функция IRR, трябва да се включи аргумент предположение, за да помогне на Microsoft Office Excel да достигне до отговора.

MIRR (values;finance_rate;reinvest_rate) -  изчислява изисквана норма на възвращаемост на направена инвестиция на базата на периодични приходи от нея, отчитайки колко процента от приходите ще се реинвестират.
Функцията MIRR е подобна на IRR, но изчислява модифицираната  вътрешна възвръщаемост. Разликата е, че MIRR отчита и парите които вземате назаем, за да финансирате инвестицията, и предполага, че ще реинвестирате получените пари. Функцията приема също, че транзакциите стават в края на период и връща коефициента, съответен на дължината на периода.
Аргументът стойности – values трябва да е масив или обръщение към област от клетки, съдържащи числа, и представлява поредица периодични плащания и приходи. В него трябва да се включи поне една положителна и една отрицателна стойност. Аргументът лихва – finance_rate е лихвата, с която се заемат парите, нужни за инвестицията. Реинвестиции – reinvest_rate е процентът реинвестирани средства.