[ФИНАНСЫ] Анализ чувствительности и «что-если» в личном финансовом плане

Когда долгосрочный личный финансовый план составлен, бывает полезно увидеть, что произойдет при изменении тех или иных его параметров. И, разумеется, такой анализ удобно проводить не только в свете долгосрочного финансового плана, но и многих других видов расчетов:

1. Что, если доходность инвестиций составит не 8%, а 12%?
2. Что, если я смогу тратить немного меньше, чем трачу сейчас?
3. Что, если я смогу рефинансировать текущий кредит с 12% до 10% годовых?
4. По какой ставке мне нужно инвестировать средства, чтобы через 5 лет накопить миллион?

Конечно, в ответе на все эти вопросы нам поможет великий «и ужасный» (хотя, конечно, на самом деле просто прекрасный) Excel. И, если в каких-то случаях можно обойтись банальной «подстановкой» тех или иных исходных данных в расчеты, то в некоторых случаях хочется увидеть результат наглядно и/или с максимальным удобством. Примеры приведу простые, но, при необходимости, их можно будет усложнять – главное здесь умение пользоваться самим Excel. Все техники (скажу по секрету) заимствованы из корпоративных финансов: с их помощью большие дяди-толстосумы оценивают свои риски при инвестировании средств в тот или иной проект. Но нам-то какая разница: просто у кого-то больше нулей на счетах, а у кого-то меньше, но главное, чтобы техника работала. Для начала необходимо подготовить плацдарм для работы: некоторый долгосрочный план. Для этого сначала заполняем исходные параметры: в первом году доходы равны 720 тысяч, расходы – 600 тысяч, а инвестиции – это разница между доходами и расходами:

Во втором и последующем годах доходы и расходы закладываем уже не «от руки», а через формулу:

При этом, конечно же, сумма инвестиций все еще равняется разницей между доходами и расходами в каждом конкретном году:

Теперь переходим к расчету накопленного капитала:

Доходность капитала заносим в таблицу «от руки», без формул. В первый год доход от инвестиций равен 0 (исходим из предпосылки, что в течение года разница между доходами и расходами просто накапливается, а по итогам года уже инвестируется). Соответственно, общая сумма накопленного капитала все еще равна 120 тысячам. Но со следующего года уже есть доход от инвестиций: он зависит от ставки доходности капитала и от суммы капитала, которая была накоплена по итогам прошлого года:

Если кто не в курсе – знак «$» в формуле просто фиксирует конкретную ячейку в расчетах так, чтобы при «протягивании» формулы в сторону (на последующие годы) формула ссылалась на одну и ту же ячейку, содержащую информацию о ставке доходности. В противном случае уже при расчете дохода в 2019 году формула сошлется на соседнюю, пустую, ячейку «C7», а нам этого не надо. Соответственно, общая сумма накопленного капитала равна сумме капитала, накопленного к концу прошлого года, плюс доход от инвестиций за текущий год, плюс сумма капитала, которую удалось сформировать за текущий год:

Все, подготовка таблицы окончена. Теперь начинаем пользоваться волшебством, на которое способен Excel. Для начала ответим на вопрос: «какой должна быть ставка доходности, чтобы при прочих равных за 5 лет накопился миллион?». Для этого в Excel существует такая функция, как «подбор параметра»:

Пользоваться ей очень просто, даже интуитивно:

Результат получен: оказывается, что при текущем уровне доходов и расходов, чтобы получить миллион спустя 5 лет, нужно где-то найти инструмент с доходность аж 26% годовых! Неслабо… Может, есть возможность более реальных цифр по доходности, при условии, что удастся тратить меньше каждый год (а, значит, больше денег оставлять для инвестирования)? Для этого подготовим еще одну небольшую табличку, все значения вносим «от руки»:

Единственная формула будет в самом углу таблицы: нужно сослаться на тот показатель, значение которого нужно отследить в зависимости от изменяющихся входных параметров:

После этого нужно выделить фрагмент таблицы: от угловой ячейки, в которой расположена ссылка на результат (общая сумма капитала через 5 лет) до крайних значений по доходности и уровню расходов:

А теперь снова обращаемся к «магии Excel». Теперь будем использовать инструмент «таблица данных»:

И производим его простейшую настройку:

Так как в столбцах выделенной таблицы («C», «D», «E» и т.д., а именно в ячейках «C12», «D12», «E12» и т.д.) расположены значения расходов, то их нужно подставлять в ячейку «B3» - ту, которая содержит информацию о расходах. В строках выделенной таблицы расположены значения доходности, поэтому их нужно подставлять в ячейку, содержащую информацию о доходности инвестиций, то есть ячейку «B7». Нажимаем «ОК» и получаем результат:

Если вдруг таблица оказалась заполнена одинаковыми значения, нужно проверить, что включен автоматический пересчет вычислений в файле:

Итак, что видно в итоговой таблице (форматирование – выделение жирным шрифтом и подсветку значений больше миллиона я сделал уже вручную, для красоты):

1. При исходной доходности в 8% расходы за год нужно сократить до уровня 540 000 рублей, то есть, на 10%, чтобы по итогам 5 лет иметь капитал более 1 млн. рублей.
2. При условии получения доходности от 12% и выше, расходы в течение года могут составить 560 тысяч рублей – и в итоге заветный миллион все ещё будет в кармане через 5 лет.

В данном случае я продемонстрировал простейшие варианты применения встроенных возможностей Excel на благо планирования и анализа возможных результатов своих действий. С опытом эти инструменты могут стать хорошими друзьями, позволяя принимать наиболее оптимальные финансовые решения.

Голосуйте за пост, подписывайтесь на мой блог, комментируйте!

Все самое интересное о финансах в моем блоге.

Источник картинки