Как рассчитать ltv в эксель
Обновлено: 06.07.2024
Отношение ссуды к стоимости определяет риск ссуды, сумму, которую ссуда будет стоить заемщику, и будет ли заемщику также необходимо приобретать частную ипотечную страховку (PMI).
Коэффициент LTV используется кредиторами перед утверждением ипотеки потенциальному заемщику. Вы можете использовать Microsoft Excel для расчета отношения ссуды к стоимости, если у вас есть сумма ипотеки и оценочная стоимость собственности.
Ключевые выводы
Отношение кредита к стоимости (LTV)
Отношение кредита к стоимости также помогает кредитору определить, придется ли заемщику платить зачастное ипотечное страхование (PMI).Как правило, чтобы не платить за частную ипотечную страховку, отношение суммы кредита к стоимости должно быть меньше или равно 75%.Иногда это может быть 80%.
Отношение суммы кредита к стоимости рассчитывается путем деления суммы ипотеки на оценочную стоимость недвижимости. Обычно оценочная стоимость равна продажной цене собственности, но кредиторы обычно требуют официальной оценки.
Отношение кредита к стоимости (LTV) и первоначальный взнос
Предположим, вы хотите купить два объекта недвижимости и рассчитать отношение суммы кредита к стоимости обоих объектов в Microsoft Excel, чтобы определить, какой из них несет больший риск и требует частного ипотечного страхования. Кредитор использует продажные цены в качестве оценочной стоимости недвижимости.
В США стандартный первоначальный взнос составляет 20%, поэтому наличие коэффициента LTV 80% или ниже обычно освобождает покупателя от необходимости покупать PMI.
Использование Excel для расчета отношения кредита к стоимости (LTV)
Чтобы рассчитать коэффициент LTV с помощью Microsoft Excel для приведенного выше примера, сначала щелкните правой кнопкой мыши столбцы A, B и C, выберите «Ширина столбца» и измените значение на 30 для каждого из столбцов. Затем нажмите CTRL и B вместе, чтобы сделать шрифт полужирным для заголовков.
Введите «Свойство 1» в ячейку B1 и введите «Свойство 2» в ячейку C1. Затем введите «Сумма ипотеки» в ячейку A2, введите «Оценочную стоимость собственности» в ячейку A3 и введите «Отношение ссуды к стоимости» в ячейку A4.
Введите «350 000 долларов США» в ячейку B2 и введите «1 850 000 долларов США» в ячейку C2. Затем введите «500 000 долларов» в ячейку B3 и «200 000 долларов США» в ячейку C3. Теперь отношение суммы кредита к стоимости можно рассчитать для обоих свойств, введя «= B2 / B3» в ячейку B4 и «= C2 / C3» в ячейку C4.
В результате отношение ссуды к стоимости для первой собственности составляет 70%, а отношение ссуды к стоимости для второй собственности составляет 92,50%. Поскольку соотношение суммы кредита к стоимости первого объекта недвижимости ниже 75%, вы, скорее всего, сможете получить ипотечный кредит, и вам не придется платить за частное страхование ипотеки.
С другой стороны, вам будет сложно получить ссуду на покупку второй собственности, потому что соотношение ссуды к ее стоимости намного превышает 75%. Вы не только не имеете права на освобождение от PMI, но, скорее всего, вообще не имеете права на получение ссуды, поскольку кредитор сочтет это слишком рискованным.
Отношение кредита к стоимости (LTV) используется финансовыми учреждениями для определения того, насколько рискованным было бы предоставлять финансирование заемщику. Чем выше ваш первоначальный взнос, тем ниже будет коэффициент LTV и тем меньше денег придется выделить банку для финансирования вашей покупки. Более низкий коэффициент LTV также означает, что человеку с меньшей вероятностью придется приобретать частную ипотечную страховку (PVI).
Чтобы узнать, в каком положении находятся ваши финансы в глазах кредитора, прежде чем подавать заявку на ипотеку, вы можете легко рассчитать коэффициент LTV в Excel, если у вас есть сумма ипотеки и оцененная стоимость недвижимости.
Я уже рассказывал о том, почему важно считать LTV, и какиспользовать метрику для планирования рекламных бюджетов.
Главная проблема LTV в том, что на сбор данных требуется время, которого обычно нет. Например, вы в течение месяца тестировали новый рекламный канал, и теперь вам нужно решить, продолжать или нет. За месяц покупок было немного, канал еще далек от точки безубыточности, но вы готовы вкладывать, если будете знать, что за год клиенты окупятся.
Сегодня я расскажу, как прогнозировать годовую выручку когорты по первому месяцу её существования. Для прогноза будем использовать линейную регрессию.
- Excel (Google-таблицы тоже подойдут, но там немного другой интерфейс);
- исторические данные о продажах (на них будем обучать модель).
Если вдруг ваш бизнес молодой, и данных за год пока нет, — ничего страшного. Постройте прогноз на тот период, за который данные есть.
Для этого упражнения я подготовил специальный файл, на котором вы можете потренироваться. Скачайте его и откройте в Excel.
Позже попросите программиста выгрузить ваши данные в таком же формате. Покажите этот файл как пример.
Когорты, в которых больше людей, скорее всего, принесут больше денег просто потому, что там больше людей. Единственный вывод, который вы сделаете из модели: «нужно больше покупателей». Не очень полезно. Вместо этого спрогнозируем среднюю выручку с покупателя. Для этого поделим итоговую выручку с когорты на количество людей.
Начинается самое интересное. Чтобы построить модель, нужно понять, как взаимосвязаны данные. Для этого построим scatter plot (такие графики мы все в школе строили).
Каждая точка на графике — одна когорта. По оси X — выручка за месяц. По оси Y — выручка за год.
На графике видна сильная и весьма логичная линейная зависимость: люди, принесшие больше за первый месяц, скорее всего, принесут больше и за год.
Вопрос в том, насколько больше?
Мы всего в паре шагов от победы. Добавьте на график линию тренда. И не забудьте вывести на экран формулу графика.
Получившаяся формула — и есть нужная нам модель. Напомню, что Y — LTV за год, а X — LTV за первый месяц. То есть:
LTV за год = 4.67 * LTV за месяц — 0,72
«Леша, ты хочешь сказать, что можно вот так вот спрогнозировать выручку за год с помощью сложения и умножения? Не может быть!»
Именно так. Но мы еще не закончили. Остался последний шаг.
Под формулой есть показатель R^2. Он показывает, насколько хорошо модель описывает имеющиеся данные. 0,93 означает «чертовски хорошо описывает».
Но нам гораздо интересней знать, с какой точностью модель прогнозирует будущее.
Скажу сразу, моделей со 100% точностью не бывает. Вообще.
Чтобы оценить точность модели, разделим имеющиеся данные на 2 группы: обучающую и тестовую.
Обучающую группу пометим нулем, тестовую — единицей.
На основе обучающей группы построим модель: добавим график с линей тренда и получим формулу.
Формула немного изменилась. Это нормально.
LTV за год = 5 * LTV за месяц — 1.72
Теперь применим формулу для прогноза на тестовой выборке.
На графике голубая линия — реальный LTV каждой когорты, а оранжевая — прогноз, результат работы модели. Смотрите, как они близко.
В статистике используют специальный показатель MSE, сводящий точность модели к одной цифре. Но чтобы не перегружать вас в одной статье, предлагаю почитать про него на «Википедии».
Вернемся к тому, зачем мы все это делали.
Напомню, что вам нужно принять решение, продолжать ли рекламную кампанию.
Допустим, что за месяц вы потратили $10 тысяч и привлекли 600 новых пользователей. Эти пользователи за месяц принесли $2400 выручки.
Стоимость одного пользователя = $10000/600 = $16,7
LTV за первый месяц = $2400/600 = $4
Подставим значения в модель:
LTV за год = 5 * $4 — 1,72 = $18,28
Прогнозный ROMI = $18,28/$16,17 = 113%.
Похоже, что рекламная кампания за год не только окупится, но и принесет небольшую прибыль. Если вас устраивает этот результат, смело продолжайте рекламироваться.
P. S. Естественно, периоды 30 и 365 дней можно менять на любые другие. Я, например, часто прогнозирую первый месяц по первому дню.
myTracker прогнозирует LTV автоматически и бесплатно — это экономит время и бюджетLifetime Value (LTV) — это показатель дохода от одного пользователя. Если говорить про мобильную аналитику — деньги, которые принесёт клиент до того, как он удалит приложение и забудет о нём.
Например, пользователь играет в игру три месяца, а затем удаляет её. В первый месяц он потратил $2, во второй — $3, а в третий — не стал ничего покупать и удалил приложение: его LTV составил $5.
У пользователя, пришедшего по рекламе, есть стоимость привлечения — это показатель CAC (Customer Acquisition Cost). Допустим, мы потратили на объявление $1000. По нему пришли 100 человек. Стоимость привлечения каждого пользователя — $10.
Если среднее LTV всех пользователей составило $5, то мы потратили на их привлечение больше денег, чем они принесут — невыгодная ситуация. Знание LTV даёт понимание, сколько можно тратить на привлечение пользователя.
Повышение LTV — задача продакт-менеджера. Среди способов — попытка удержать пользователя или повысить его средний чек. А в этой статье мы поговорим только о том, как рассчитать LTV.
Считайте прибыль на определённый день
Может получиться так, что пользователь принесёт больше денег, чем стоило его привлечение, но сделает это, например, только через два года. А приложение должно окупиться через год.
Основной совет — не считать LTV в его классическом понимании. С точки зрения бизнеса, сам по себе показатель, сколько денег пользователь принесёт за всё время игры, имеет мало практического смысла.
Куда важнее понимать, какой доход принесет привлеченный пользователь в каждый его день с момента регистрации. Это дает нам понимание, когда пользователь окупится и совпадает ли это с нашими ожиданиями. Такие данные позволяют строить прогнозы и модели окупаемости.
Если бизнес не готов ждать окупаемости бесконечно, то лучше считать не LTV, а на какой день вернутся деньги. Эта задача решается проще.
Посчитать прибыль на определённый день поможет способ на основе накопительного ARPU, о котором мы расскажем дальше.
Делите пользователей на группы
Если анализировать всех сразу, получится среднее число, которое будет далёким от реальности. Поэтому пользователей нужно делить на группы — они называются когортами. Людей можно сгруппировать по дате регистрации, территориальным признакам, после какой рекламы пришли и так далее.
Например, люди, которые пришли после рекламы в Facebook, — это одна группа. Которые пришли из «ВКонтакте» — другая.
А если у вас много данных, то пробуйте считать LTV для одного пользователя, а не группы. Эта информация будет ещё ценнее. Например, от этих данных может зависеть монетизация конкретного человека: показывать ему рекламу или предлагать внутриигровые покупки.
Помните о пользователях с аномальным поведением
Почти в каждой группе есть игроки, чьё поведение отличается от других. Например, все тратят по $5 в неделю, а кто-то один — по $1000. Из-за него расчёт LTV получится неточным — этого человека лучше на время исключить. Но при этом совсем забывать о таких людях не нужно.
Если полностью исключить топ-плательщиков из расчётов окупаемости, можно не учесть часть прибыли. В результате вы ограничите себя в объеме инвестиций в успешный проект. Вариант решения проблемы «китов» — выбирать размер когорт таким образом, чтобы появление топ-плательщика было статистически прогнозируемо.
Не стоит доверять средним показателям по рынку
Даже с небольшим LTV у вас может быть всё хорошо, если вы умеете оптимизировать затраты и дешёво закупать платящих игроков.
Невозможно точно предсказать, сколько человек будет пользоваться приложением и сколько денег заплатит. Мы можем сделать лишь примерный прогноз. Каждый аналитик должен выбрать, какой из способов лучше подходит для его приложения, либо придумать свой.
На основе удержания — сложный способ, придётся сделать много расчётов. Подойдёт продвинутым аналитикам, если нужно определить LTV как можно точнее. Он работает хорошо, если пользователи платят стабильно, без резких изменений.
Накопительный ARPU — способ, который можно применять при наличии небольшого количества данных и за недолгий срок. Позволяет точнее понять, на какой день окупится привлечение пользователя.
Этот способ хорош тем, что здесь мы точнее определяем, сколько в среднем дней или месяцев люди пользуются нашим приложением — Lifetime. Чтобы его рассчитать, нужно обратиться к другой метрике — показатель удержания (Retention).
Чтобы объяснить, что такое показатель удержания, возьмём простой пример. Мы запустили приложение — в первый день в него зашли 100 человек. А на второй день из них зашли только 60. Значит, показатель удержания на второй день — 60%. Его изображают в виде такого графика.
Когда приложение молодое, мы не знаем, сколько люди будут в него играть. Поэтому показатель удержания приходится прогнозировать.
Наш фактический график удержания изгибается примерно равномерно. Поэтому, если мы найдём функцию, график которой будет изгибаться похожим образом, то сможем сделать прогноз.
Есть несколько формул. Использовать можно все. Просто одна из них построит новый график чуть точнее, чем остальные. Понять, какая именно, можно только попробовав все. Если ни одна из них не подошла, можно поискать на просторах интернета другие. Например, здесь. Вот некоторые из формул:
«X» в уравнении — период времени. Например, нам известно удержание за 30 дней. Поэтому попробуем построить график на чуть больший срок — 90 суток. Соответственно, наше «X» — 90.
«A, B, C, D» — параметры функции. Это определённые числа, которые нужно подобрать так, чтобы новый график был максимально схож с фактическим. Это можно сделать вручную, либо воспользоваться инструментом Solver для Excel. Как им пользоваться, можно найти тут.
Строим графики и сравниваем их с фактическим. Должно получиться похоже на это:
Подробное руководство по графику удержания (Retention) можно найти в этой статье.
Итак, мы построили график на 90 дней. Теперь нужно узнать, сколько в среднем платит один игрок в месяц — ARPU.
Берём валовую прибыль за месяц и делим её на количество клиентов, которые пользовались приложением в этом месяце. Например, игра работает 30 дней. За это время мы заработали $5000, а поиграли в нашу игру 100 человек. Получается, в среднем каждый игрок приносит нам в месяц по $50 — это ARPU.
Теперь, чтобы посчитать LTV, нужно поделить график удержания на месяцы. У нас он на 90 дней, значит делим его на три части — по 30 дней. И смотрим, какой показатель удержания был в конце каждого месяца. Напомним, что в наших примерах мы завысили и округлили все цифры, чтобы было проще считать, — в реальности они намного ниже.
Кроме того, ссуда с высоким коэффициентом LTV может потребовать от заемщика приобретения ипотечной страховки, чтобы компенсировать риск для кредитора. Этот вид страхования называется частным ипотечным страхованием (PMI).
Ключевые моменты
Понимание отношения кредита к стоимости (LTV)
Заинтересованные покупатели могут легко рассчитать коэффициент LTV дома. Это формула:
Коэффициент LTV рассчитывается путем деления суммы займа на оценочную стоимость недвижимости, выраженную в процентах. Например, если вы покупаете дом, оцененный в 100 000 долларов по его оценочной стоимости, и вносите авансовый платеж в размере 10 000 долларов , вы будете занимать 90 000 долларов. В результате коэффициент LTV составляет 90% (т.е. 90 000/100 000).
Кредиторы оценивают коэффициент LTV, чтобы определить уровень подверженности риску, который они принимают на себя при андеррайтинге ипотеки. Когда заемщики запрашивают ссуду на сумму, которая равна оценочной стоимости или близка к ней (и, следовательно, имеет более высокий коэффициент LTV), кредиторы считают, что существует большая вероятность того, что ссуда окажется в невыгодном положении . Это связано с тем, что в собственности очень мало собственного капитала. В результате в случае обращения взыскания кредитор может столкнуться с трудностями при продаже дома на сумму , достаточную для покрытия непогашенного остатка по ипотеке и при этом получить прибыль от сделки.
Основными факторами, влияющими на коэффициент LTV, являются размер первоначального взноса, продажная цена и оценочная стоимость недвижимости. Самый низкий коэффициент LTV достигается при более высоком первоначальном взносе и более низкой цене продажи.
Как кредиторы используют LTV
Большинство кредиторов предлагают соискателям ипотечных кредитов и жилищного капитала самую низкую процентную ставку, когда их коэффициент LTV составляет 80% или ниже.1 Более высокий коэффициент LTV не исключает утверждения заемщиками для получения ипотеки, хотя проценты по ссуде могут повышаться по мере увеличения коэффициента LTV. Например, заемщик с коэффициентом LTV 95% может получить ипотечный кредит. Однако их процентная ставка может быть на полный процентный пункт выше, чем процентная ставка, предоставляемая заемщику с коэффициентом LTV 75%.
Если коэффициент LTV превышает 80%, от заемщика могут потребовать приобрести частную ипотечную страховку (PMI) . Это может добавлять от 0,5% до 1% к общей сумме кредита на годовой основе. Например, PMI со ставкой 1% по ссуде в 100 000 долларов добавит дополнительно 1000 долларов к общей сумме, выплачиваемой в год (или 83,33 доллара в месяц). Платежи PMI требуются до тех пор, пока коэффициент LTV не станет 80% или ниже. Коэффициент LTV будет уменьшаться по мере того, как вы выплачиваете ссуду, и по мере того, как стоимость вашего дома со временем увеличивается.
В целом, чем ниже коэффициент LTV, тем больше вероятность того, что ссуда будет одобрена, и тем ниже, вероятно, будет процентная ставка. Кроме того, как заемщик, менее вероятно, что вам придется приобретать частную ипотечную страховку (PMI).
Хотя это не закон, что кредиторы требуют 80% LTV, чтобы заемщики могли избежать дополнительных затрат на PMI, это практика почти всех кредиторов. Исключения из этого требования иногда делаются для заемщиков, которые имеют высокий доход, низкий долг или большой инвестиционный портфель.
Пример LTV
Например, предположим, что вы покупаете дом стоимостью 100 000 долларов. Однако владелец готов продать его за 90 000 долларов. Если вы вносите авансовый платеж в размере 10 000 долл. США, ваша ссуда составляет 80 000 долл. США, в результате чего коэффициент LTV составляет 80% (т. Е. 80 000/100 000). Если бы вы увеличили сумму своего первоначального взноса до 15 000 долларов, ваша ипотечная ссуда теперь составляет 75 000 долларов. Это сделает ваш коэффициент LTV 75% (т. Е. 75 000/100 000).
Варианты правил отношения ссуды к стоимости
В отношении требований к коэффициенту LTV для разных типов ссуд могут применяться разные правила.
Кредиты FHA
Ссуды VA и USDA
Фанни Мэй и Фредди Мак
HomeReady от Fannie Mae и HomeReady от Freddie Mac Возможные ипотечные программы для заемщиков с низким доходом позволяют коэффициент LTV на уровне 97%.Однако они требуют ипотечного страхования, пока коэффициент не упадет до 80%.23
LTV против комбинированного LTV (CLTV)
Однако рассмотрим пример, если у него также есть вторая ипотека в размере 30 000 долларов и HELOC в размере 20 000 долларов. Суммарная стоимость кредита теперь составляет (100 000 долларов США + 30 000 долларов США + 20 000 долларов США / 200 000 долларов США) = 75%; гораздо более высокое соотношение.
Эти комбинированные соображения особенно важны, если залогодержатель не выполняет свои обязательства и противники обращаются взыскание.
Недостатки ссуды на стоимость (LTV)
Главный недостаток информации, которую предоставляет LTV, заключается в том, что она включает только основную ипотеку, которую домовладелец должен, и не включает в свои расчеты другие обязательства заемщика, такие как вторая ипотека или ссуда под залог собственного капитала. Таким образом, CLTV является более всеобъемлющим показателем способности заемщика выплатить жилищный заем.
Читайте также: