Як розрахувати теперішню вартість в Excel при різних платежах

Іноді нам доводиться розраховувати поточну вартість активу або майбутню вартість в Microsoft Excel Цей розрахунок може бути виконаний в декількох випадках. Ви шукаєте рішення для розрахунку теперішньої вартості від майбутньої вартості в Excel У цій статті ми обговоримо як розрахувати теперішню вартість в Excel при різних платежах на 5 простих прикладах .

Завантажити Практичний посібник

Ви можете завантажити Робоча книга Excel звідси.

Розрахувати теперішню вартість для різних платежів.xlsx

5 простих прикладів розрахунку теперішньої вартості в Excel для різних платежів

Зараз ми побачимо 5 прості приклади з поясненнями для розрахунку теперішньої вартості в Excel з різними платежами з використанням фотоелектрична функція Тут ми використали 5 види платежів, які потребують розрахунку теперішньої вартості. Тож, не відкладаючи на потім, почнемо.

1. розрахунок теперішньої вартості для одноразового платежу

У цьому прикладі ми розрахуємо поточну вартість в Excel для одноразового платежу. Якщо ми вирішили інвестувати гроші одноразовим платежем, то поточна вартість інвестиції буде залежати від майбутньої вартості ( FV ) замість періодичного платежу ( ПМС Припустимо, що у нас є набір даних у вигляді Excel ( B4:C8 ), де Річна процентна ставка , Кількість років та Майбутня вартість Тепер нам необхідно розрахувати теперішню вартість платежу для одноразового платежу, використовуючи фотоелектрична функція .

Виконайте наведені нижче кроки для розрахунку теперішньої вартості від одного платежу.

Сходинки:

• На початку нам потрібно виділити комірку C8 де ми хочемо зберегти поточну вартість.
• Далі для розрахунку теперішньої вартості даного одноразового платежу введіть формулу:

• Нарешті, після натискання кнопки Увійдіть ми зможемо побачити Теперішня вартість єдиного внеску.

Детальніше: Як розрахувати теперішню вартість одноразової виплати в Excel (3 способи)

2. розрахувати теперішню вартість для періодичного платежу

Для того, щоб розрахувати теперішню вартість для періодичного платежу, ми повинні розділити річну ставку на кількість періодів у році, щоб перетворити її на періодичну ставку. Знову ж таки, нам потрібно помножити термін у роках на кількість періодів у році, щоб отримати загальну кількість періодів. Тепер, припустимо, що у нас є набір даних ( B4:C9 ) в Excel Тут ми бачимо, що інвестиції становлять $200 на місяць для 5 років на рік 5% річна процентна ставка.

Ми можемо розрахувати теперішню вартість цього періодичного платежу, виконавши наведені нижче кроки.

Сходинки:

• По-перше, виділіть комірку C9 де ви хочете зберегти теперішню вартість.
• По-друге, для обчислення майбутнього значення заданих даних введіть формулу:

• Наостанок, натисніть Увійдіть для отримання теперішньої вартості.

3. розрахунок приведеної вартості регулярного грошового потоку

Для того, щоб розрахувати теперішню вартість в Excel при різних платежах з регулярним грошовим потоком, у нас є набір даних ( B4:E12 ), де ми можемо побачити деякі Періоди , обов’язкове повернення а деякі регулярні грошові потоки з $200 для 4 Припустимо, нам потрібно обчислити Теперішня вартість Для цього спочатку розрахуємо теперішню вартість кожного грошового потоку окремо, а потім просумуємо окремі грошові потоки, щоб отримати Загальна теперішня вартість .

Виконайте наведені нижче кроки для розрахунку теперішньої вартості для регулярного платежу.

Сходинки:

• Спочатку виділяємо комірку D8 та введіть наступну формулу:

• По-друге, виділіть комірку D8 і перетягніть Ручка наповнення касовий апарат D11 і ми отримаємо PV коефіцієнти для всіх періодів.

• По-третє, щоб розрахувати теперішню вартість для окремих періодів, виберіть комірку E8 та введіть наступну формулу:

• Потім натисніть Увійдіть і отримаємо теперішню вартість за період 1 .

• Після цього виберіть комірку E8 і перетягніть Ручка наповнення касовий апарат E11 і отримаємо теперішню вартість для всіх періодів.

• Тепер настав час підсумувати всі теперішні вартості, щоб отримати загальну теперішню вартість. Для цього ми будемо використовувати функція SUM Тут введіть формулу в комірку E12 :

• Наостанок, натисніть Увійдіть і отримуємо загальну теперішню вартість.

Детальніше: Як розрахувати теперішню вартість майбутніх грошових потоків в Excel

4. розрахунок теперішньої вартості нерегулярних грошових потоків

Тепер розглянемо, як розрахувати теперішню вартість в Excel при нерегулярних грошових виплатах. Припустимо, у нас є набір даних ( B4:D12 ) в Excel, де ми можемо побачити деякі Періоди , обов’язкове повернення а деякі нерегулярний Грошові потоки Скажімо, нам потрібно обчислити Теперішня вартість Для цього спочатку розрахуємо теперішню вартість кожного грошового потоку окремо, а потім просумуємо окремі грошові потоки, щоб отримати Загальна теперішня вартість .

Для розрахунку теперішньої вартості для нерегулярних грошових потоків виконайте наведені нижче дії.

Сходинки:

• Для початку виділіть комірку D8 .
• Після цього для отримання теперішньої вартості грошового потоку в комірці C8 введіть формулу:

• Згодом, коли ми натиснемо Увійдіть отримаємо теперішню вартість відповідного грошового потоку.

• Аналогічно, щоб отримати всі теперішні вартості грошових потоків, нам потрібно перетягнути Ручка наповнення поки ми не вийдемо на фінальний грошовий потік.
• В результаті ми отримали всі теперішні вартості окремих грошових потоків.

• Тепер настав час підсумувати значення окремих грошових потоків для отримання загальної теперішньої вартості. Для цього ми будемо використовувати функція SUM Тут введіть формулу в комірку D12 :

• Наостанок, натисніть Увійдіть і отримуємо загальну теперішню вартість.

Детальніше: Як розрахувати теперішню вартість нерівномірних грошових потоків в Excel

5. створення калькулятора теперішньої вартості

Якщо ми хочемо створити PV калькулятор, який може обробляти як періодичні, так і разові платежі, нам потрібно буде скористатися Excel PV Для початку, як на скріншоті нижче, призначте комірки для всіх аргументів, а також для необов’язкових.

Тоді визначте аргументи таким чином:

ставка (періодична процентна ставка): C5/C10 (річна процентна ставка/періоди на рік)

nпер (загальна кількість платіжних періодів): C6*C10 (кількість років * періоди в році)

pmt (сума періодичного платежу): C7

pv (початкові інвестиції): C8

тип (при настанні строку сплати): C9

періодів компаундування на рік: C10

Тепер виконайте наведені нижче кроки для розрахунку теперішньої вартості.

Сходинки:

• По-перше, потрібно виділити комірку C11 та введіть формулу:

• Нарешті, натиснувши кнопку Увійдіть бажане поточне значення буде видно, як показано нижче.

Докладніше: Як застосувати формулу теперішньої вартості ануїтету в Excel

Як розрахувати майбутню вартість в Excel з різними виплатами

Ми також можемо розрахувати майбутню вартість на основі поточної вартості чогось. Це можна зробити багатьма способами і в багатьох сценаріях. Ми взяли той самий набір даних нижче, щоб розрахувати майбутню вартість на основі поточної вартості одного платежу, щоб показати вам приклад.

Виконайте наведені нижче дії, щоб розрахувати майбутню вартість від одного платежу.

Сходинки:

• По-перше, потрібно виділити комірку C8 де ми хочемо зберегти майбутню вартість.
• Далі, щоб розрахувати теперішню вартість даного одноразового платежу, введіть формулу:

• Таким чином, після натискання кнопки Увійдіть ми зможемо побачити Майбутня вартість єдиного внеску.

Читати далі: Як розрахувати майбутню вартість в Excel з різними виплатами

Важливі моменти для запам’ятовування

• Ви повинні правильно визначити аргументи, якщо ви хочете розрахувати теперішню вартість за допомогою калькулятора теперішньої вартості.
• Ви повинні розуміти, для якого випадку ви розраховуєте теперішню вартість від майбутньої вартості, і слідувати аналогічному прикладу, щоб отримати правильний результат.

Висновок

Таким чином, виконавши вищеописані кроки, ви зможете легко навчитися як розрахувати теперішню вартість в Excel при різних платежах Сподіваємось, це буде корисно. Слідкуйте за ExcelWIKI Не забувайте залишати свої коментарі, пропозиції чи запитання у розділі коментарів нижче.

Hugh West

Г’ю Вест — досвідчений тренер і аналітик Excel із понад 10-річним досвідом роботи в галузі. Він має ступінь бакалавра з бухгалтерського обліку та фінансів і ступінь магістра з ділового адміністрування. Г’ю має пристрасть до викладання та розробив унікальний підхід до викладання, який легко зрозуміти та дотримуватися. Його експертне знання Excel допомогло тисячам студентів і професіоналів у всьому світі вдосконалити свої навички та досягти успіху в кар’єрі. У своєму блозі Г’ю ділиться своїми знаннями зі світом, пропонуючи безкоштовні навчальні посібники з Excel та онлайн-навчання, щоб допомогти окремим особам і компаніям повністю розкрити свій потенціал.

Розрахунок NPV в Excel – формула чистої приведеної вартості

Чиста теперішня вартість або чиста поточна вартість це основний елемент фінансового аналізу, який показує, чи буде проект прибутковим чи ні. Чому чиста приведена вартість так важлива? Тому що основна фінансова концепція стверджує, що гроші, які потенційно можуть бути отримані в майбутньому, коштують менше, ніж та ж сума грошей, яку ви маєте зараз. Чиста приведена вартість дисконтує грошові потоки, які очікуються в майбутньому, назад до теперішнього часу.показати свою сьогоднішню цінність.

Microsoft Excel має спеціальну функцію для розрахунку NPV, але її використання може бути складним, особливо для людей, які не мають достатнього досвіду у фінансовому моделюванні. Мета цієї статті – показати, як працює функція NPV в Excel, і вказати на можливі помилки при розрахунку чистої теперішньої вартості ряду грошових потоків в Excel.

Що таке чиста приведена вартість (NPV)?

Чиста теперішня вартість (NPV) – це вартість ряду грошових потоків протягом усього терміну реалізації проекту, приведена до теперішнього часу.

Простіше кажучи, NPV можна визначити як теперішню вартість майбутніх грошових потоків за вирахуванням початкових інвестиційних витрат:

NPV = PV майбутніх грошових потоків – Початкові інвестиції

Щоб краще зрозуміти ідею, давайте трохи заглибимося в математику.

Для одного грошового потоку теперішня вартість (PV) розраховується за цією формулою:

• r – дисконтна або процентна ставка
• i – період руху грошових коштів

Наприклад, щоб отримати 110 доларів США (майбутня вартість) через 1 рік (i), яку суму ви повинні вкласти сьогодні на свій банківський рахунок, який пропонує 10% річних (r)? Наведена вище формула дає відповідь на це питання:

Іншими словами, 100 доларів – це поточна вартість 110 доларів, які очікується отримати в майбутньому.

Чиста теперішня вартість (NPV) підсумовує теперішні вартості всіх майбутніх грошових потоків для приведення їх до єдиної точки в сьогоденні. І оскільки ідея “чистої” полягає в тому, щоб показати, наскільки прибутковим буде проект після врахування початкових капітальних інвестицій, необхідних для його фінансування, сума початкових інвестицій віднімається від суми всіх теперішніх вартостей:

• r – дисконтна або процентна ставка
• n – кількість часових періодів
• i – період руху грошових коштів

Оскільки будь-яке ненульове число, піднесене до нульового степеня, дорівнює 1, ми можемо включити початкові інвестиції в цю суму. Зверніть увагу, що в цій компактній версії формули NPV, i=0, тобто початкові інвестиції здійснюються в період 0.

Наприклад, для знаходження NPV для ряду грошових потоків (50, 60, 70), дисконтованих за ставкою 10% і початковою вартістю $100, можна скористатися такою формулою:

Як чиста приведена вартість допомагає оцінити фінансову життєздатність запропонованої інвестиції? Передбачається, що інвестиція з позитивним значенням NPV буде прибутковою, а інвестиція з від’ємним значенням NPV – збитковою. Ця концепція покладена в основу Правило чистої теперішньої вартості в якому йдеться про те, що слід брати участь лише в проектах з позитивною чистою приведеною вартістю.

Функція NPV Excel

Функція NPV в Excel повертає чисту теперішню вартість інвестиції на основі дисконту або процентної ставки та ряду майбутніх грошових потоків.

Синтаксис функції NPV Excel наступний:

NPV(ставка, значення1, [значення2], . )

• Ставка (обов’язково) – дисконтна або процентна ставка за один період, яка повинна бути вказана у відсотках або у вигляді відповідного десяткового числа.
• значення1, [значення2], . – числові значення, що представляють собою серію регулярних грошових потоків. Значення1 є обов’язковим, наступні значення є необов’язковими. У сучасних версіях Excel 2007-2019 можна подавати до 254 аргументів значення, в Excel 2003 і старіших версіях – до 30 аргументів.

Функція NPV доступна в Excel 365 – 2000.

• Для розрахунку теперішньої вартості ануїтету використовуйте функцію PV в Excel.
• Щоб оцінити прогнозовану рентабельність інвестицій, зробіть розрахунок IRR.

4 речі, які варто знати про функцію NPV

Щоб переконатися, що ваша формула NPV в Excel розраховується правильно, будь ласка, пам’ятайте про ці факти:

• Значення повинні відбуватися на рівні кінець кожного періоду Якщо перший грошовий потік (первісна інвестиція) відбувається на початок першого періоду використовуйте одну з цих формул NPV.
• Значення повинні бути вказані в хронологічний порядок і рівномірно рознесені в часі .
• Використання негативний значення для представлення відтоку (виплати грошових коштів) та позитивний значення для відображення надходжень (отриманих грошових коштів).
• Тільки числові значення Порожні клітинки, текстові представлення чисел, логічні значення та значення помилок ігноруються.

Як працює функція NPV в Excel

Використання функції NPV в Excel є дещо складним через спосіб її реалізації. За замовчуванням передбачається, що інвестиція здійснюється за один період до значення1 З цієї причини формула NPV в чистому вигляді працює коректно лише за умови, що ви задаєте початкові інвестиційні витрати через один період часу Не сьогодні!

Для ілюстрації цього розрахуємо чисту теперішню вартість вручну та за допомогою формули NPV в Excel і порівняємо результати.

Скажімо, у вас є ставка дисконтування в B1, ряд грошових потоків в B4:B9 і номери періодів в A4:A9.

Надайте вищезазначені посилання в цій загальній формулі PV:

PV = майбутня вартість/(1+ставка)^період

І ви отримаєте наступне рівняння:

Ця формула потрапляє в комірку C4, а потім копіюється в наступні комірки. Завдяки розумному використанню абсолютних і відносних посилань на комірки, формула ідеально підлаштовується під кожен рядок, як показано на скріншоті нижче.

Зверніть увагу, що ми також розраховуємо теперішню вартість початкових інвестицій, оскільки початкові інвестиційні витрати становлять після 1 року тому вона також дисконтується.

Після цього ми підсумовуємо всі теперішні вартості:

А тепер зробимо NPV за допомогою функції Excel:

Як бачимо, результати обох розрахунків точно збігаються:

Але що робити, якщо початкові витрати відбуваються при початок першого періоду як це зазвичай буває?

Оскільки початкова інвестиція здійснюється сьогодні, до неї не застосовується дисконтування, і ми просто додаємо цю суму до суми теперішньої вартості майбутніх грошових потоків (оскільки це від’ємне число, воно фактично віднімається):

І в даному випадку ручний розрахунок та функція NPV Excel дають різні результати:

Чи означає це, що ми не можемо покладатися на формулу NPV в Excel і повинні розраховувати чисту теперішню вартість вручну в цій ситуації? Звичайно, ні! Вам просто потрібно буде трохи налаштувати функцію NPV, як пояснюється в наступному розділі.

Як розрахувати NPV в Excel

При здійсненні первинної інвестиції на початок першого періоду ми можемо розглядати його як грошовий потік на кінець попереднього періоду (тобто періоду 0). З огляду на це, є два простих способи знайти NPV в Excel.

Excel NPV формула 1

Залишіть початкові витрати поза діапазоном значень і відніміть їх від результату функції NPV. Оскільки початкові витрати, як правило, вводяться як від’ємне число ви фактично виконуєте операцію додавання:

NPV (ставка, цінності) + початкова вартість

У цьому випадку функція NPV Excel просто повертає теперішню вартість нерівномірних грошових потоків. Оскільки нам потрібна “чиста” вартість (тобто теперішня вартість майбутніх грошових потоків за вирахуванням початкових інвестицій), ми віднімаємо початкові витрати за межами функції NPV.

Формула NPV Excel 2

Включіть початкову вартість в діапазон значень і помножте результат на (1 + ставка).

У цьому випадку функція NPV Excel дасть результат станом на період -1 (як якщо б початкова інвестиція була зроблена за один період до періоду 0), ми повинні помножити її вихід на (1 + r), щоб перенести NPV на один період вперед у часі (тобто з i = -1 до i = 0). Будь ласка, дивіться компактну формулу формули NPV.

NPV (ставка, значень) * (1+ставка)

Яку формулу використовувати – справа ваших особистих уподобань. Я особисто вважаю, що перша є простішою і зрозумілішою.

Калькулятор NPV в Excel

Тепер давайте подивимося, як за допомогою наведених вище формул на реальних даних можна зробити власний калькулятор NPV в Excel.

Припустимо, що у вас є початкові витрати в B2, ряд майбутніх грошових потоків в B3:B7 і необхідна норма прибутку в F1. Для знаходження NPV використовуйте одну з наступних формул:

Зверніть увагу, що першим аргументом вартості є грошовий потік у періоді 1 (B3), початкова вартість (B2) не включається.

=NPV(F1, B2:B7) * (1+F1)

Ця формула включає початкову вартість (В2) в діапазон значень.

На скріншоті нижче показано наш калькулятор NPV в Excel в дії:

Щоб переконатися, що наші формули NPV в Excel є коректними, перевіримо результат ручними розрахунками.

По-перше, ми знаходимо теперішню вартість кожного грошового потоку, використовуючи формулу PV, описану вище:

Далі необхідно скласти всі теперішні вартості та відняти початкову вартість інвестицій:

. і бачимо, що результати всіх трьох формул абсолютно однакові.

Примітка: у цьому прикладі ми маємо справу з річними грошовими потоками та річною ставкою. Якщо ви хочете знайти щоквартальний або щомісяця NPV в Excel, обов’язково скоригуйте ставку дисконтування відповідним чином, як пояснюється в цьому прикладі.

Різниця між PV та NPV в Excel

У фінансах як PV, так і NPV використовуються для оцінки поточної вартості майбутніх грошових потоків шляхом дисконтування майбутніх сум до теперішньої вартості. Але вони відрізняються в одному важливому аспекті:

• Теперішня вартість (PV) – відноситься до всіх майбутніх грошових надходжень у певному періоді.
• Чиста теперішня вартість (NPV) – різниця між теперішньою вартістю грошових надходжень та теперішньою вартістю грошових відтоків.

Іншими словами, PV враховує лише грошові надходження, в той час як NPV також враховує початкові інвестиції або витрати, що робить його чистим показником.

У Microsoft Excel є дві суттєві відмінності між функціями:

• Функція NPV може розраховувати нерівномірні (змінні) грошові потоки. Функція PV вимагає, щоб грошові потоки були постійними протягом усього терміну дії інвестиції.
• У випадку з NPV грошові потоки повинні виникати в кінці кожного періоду. PV може обробляти грошові потоки, які виникають в кінці та на початку періоду.

Різниця між NPV та XNPV в Excel

XNPV – це ще одна фінансова функція Excel, яка обчислює чисту теперішню вартість інвестицій. Основна відмінність між функціями полягає в наступному:

• NPV розглядає всі часові періоди як рівний .
• XNPV дозволяє вказати дати, які відповідають кожному грошовому потоку. З цієї причини функція XNPV є набагато точнішою, коли має справу з серією грошових потоків на нерегулярні інтервали .

На відміну від NPV, функція Excel XNPV реалізована “нормально” – перше значення відповідає відтоку, який відбувається на початку інвестування. Всі наступні грошові потоки дисконтуються виходячи з 365-денного року.

З точки зору синтаксису, функція XNPV має один додатковий аргумент:

XNPV (курс, значення, дати)

Для прикладу розглянемо обидві функції на одному наборі даних, де F1 – ставка дисконтування, B2:B7 – грошові потоки, C2:C7 – дати:

Якщо грошові потоки становлять розподілений рівномірно Завдяки інвестиціям, функції NPV та XNPV повертають дуже близькі показники:

У разі нерегулярні інтервали різниця між результатами є дуже суттєвою:

Поширені помилки при розрахунку NPV в Excel

Через досить специфічну реалізацію функції NPV при розрахунку чистої теперішньої вартості в Excel допускається багато помилок. Прості приклади, наведені нижче, демонструють найбільш типові помилки та способи їх уникнення.

Нерегулярні інтервали

Функція NPV Excel передбачає, що всі періоди грошових потоків є рівний Якщо ви надаєте різні інтервали, скажімо, роки та квартали або місяці, чиста теперішня вартість буде неправильною через неузгодженість часових періодів.

Пропущені періоди або грошові потоки

NPV в Excel не розпізнає пропущені періоди і ігнорує порожні клітинки. Для коректного розрахунку NPV, будь ласка, обов’язково надайте послідовний місяці, квартали або роки та пропозиція нуль цінності для періодів часу, які мають нульові грошові потоки.

Ставка дисконтування не відповідає реальним часовим періодам

Функція Excel NPV не може автоматично пристосувати надану ставку до заданих часових інтервалів, наприклад, річну ставку дисконтування до щомісячних грошових потоків. Користувач несе відповідальність за надання відповідна ставка за період .

Неправильний формат курсу

Дисконт або процентна ставка повинна бути надана у вигляді відсоток або відповідний десяткове число Наприклад, 10-відсоткова ставка може бути подана як 10% або 0,1. Якщо ви введете ставку як число 10, Excel сприйме її як 1000%, і NPV буде розрахована неправильно.

Ось як використовувати NPV в Excel для знаходження чистої теперішньої вартості інвестицій. Щоб більш детально ознайомитися з формулами, розглянутими в цьому підручнику, будь ласка, завантажте наш зразок калькулятора NPV для Excel.

Дякуємо за увагу і сподіваємося побачити Вас на нашому блозі наступного тижня!

Michael Brown

Майкл Браун — відданий ентузіаст технологій із пристрастю до спрощення складних процесів за допомогою програмних засобів. Маючи понад десятирічний досвід роботи в технологічній індустрії, він відточив свої навички роботи з Microsoft Excel і Outlook, а також із Google Таблицями та Документами. Блог Майкла присвячений тому, щоб поділитися своїми знаннями та досвідом з іншими, надаючи прості поради та навчальні посібники для підвищення продуктивності та ефективності. Незалежно від того, чи є ви досвідченим професіоналом чи початківцем, блог Майкла пропонує цінну інформацію та практичні поради щодо отримання максимальної користі від цих основних програмних інструментів.