Сейчас расширим ваши горизонты в Экселе.
Когда мы копили на айфон с помощью Экселя и банковского вклада, то строили таблицы — это нам было нужно, чтобы увидеть сумму, которая получится в конце. У того решения был один минус — мы сами должны были задавать стартовые суммы и проверять, сработает или нет, будет у нас нужная сумма в конце года или не накопим.
Сегодня будет другое решение, в котором мы указываем финансовую цель, а Эксель сам нам скажет, на какую сумму нужно пополнять счёт, чтобы всё сошлось.
👉 Для этого мы воспользуемся возможностью «Подбор параметра». Это работает ровно так же, как звучит: Эксель за нас подгонит цифры, чтобы получить нужный результат. Никакой математической магии, просто перебор. Компьютерам так можно.
Исходная ситуация
- у нас есть стартовая сумма, которую мы можем сразу положить на счёт;
- каждый месяц мы добавляем фиксированную сумму, чтобы накопить на телефон (как пример);
- банк даёт нам какую-то процентную ставку на год, но проценты по счёту начисляет каждый месяц.
Нам нужно будет подобрать такую сумму ежемесячных взносов, чтобы в конце получилась стоимость телефона.
Исходная таблица
Сначала подготовим пустую таблицу, в которую будем заносить все данные:
Теперь заполним таблицу так:
- стартовый взнос: 10 000 рублей;
- процентная ставка: 6 процентов годовых;
- срок вклада: 12 месяцев.
Ежемесячный взнос мы пока не знаем, поэтому оставим пустым:
Считаем будущую стоимость
В Экселе есть встроенная функция БС (в английской версии это FV) — она рассчитывает размер дохода по вкладу с фиксированной процентной ставкой. Работает она так:
БС(Ставка; Количество платежей; Размер каждого платежа; Стартовый взнос)
Ставка — сколько процентов начисляется за каждый период. Так как у нас проценты начисляются ежемесячно, то ставка должна показывать проценты за месяц. Для этого берём размер годовой ставки и делим на 12 месяцев.
Количество платежей — сколько раз мы будем пополнять счёт. В нашем случае — тоже 12 месяцев.
Размер каждого платежа — какую сумму будем добавлять каждый месяц. Особенность Экселя в том, что его нужно поставить со знаком «минус».
Стартовый взнос — он может быть, а может и не быть. Если его нет, можно не заполнять.
Используем эти знания и подготовим формулу, которая посчитает накопления за всё время:
=БС(B2/B3; B3; -B5; -B1)
Нажимаем энтер, чтобы увидеть результат:
Получается, что если мы ничего не будем больше вносить на счёт, то он увеличится на 616 рублей.
Подбираем размер платежа
Теперь у нас всё готово, чтобы Эксель нам рассчитал размер ежемесячного платежа. Для этого в меню Экселя выбираем Данные → Анализ «Что, если» → Подбор параметров:
Появится окно, где нам нужно ввести параметры подбора:
Допустим, новый телефон стоит 55 000 рублей, а значит, именно такую сумму нам нужно накопить за год. Формула с подсчётом наших накоплений хранится в ячейке B7, поэтому её указываем в первом параметре. Эту ячейку Эксель будет постоянно проверять, получилось что-то или нет.
Значение — тут мы указываем, какое значение нам нужно чтобы появилось в ячейке B7. Раз телефон стоит 55 000, то такое же значение указываем вторым параметром.
Третий параметр — с какой ячейкой Эксель будет экспериментировать, чтобы в B7 появилось нужное значение. В нашем случае — с первоначальным взносом, с ячейкой B5.
Нажимаем ОК и смотрим на результат:
Эксель решил нашу задачу и выдал ответ: нужно вносить на счёт 3798 рублей, чтобы за год накопилось ровно 55 тысяч.
Теперь мы можем поручать Экселю считать и подбирать количество рабочих часов, размер зарплаты, KPI работников и что угодно ещё.
