{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Параметры кредита","Настройка калькулятора","Расчет графика платежей","Регионы"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":32,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Параметры кредита","index":0,"n_rows":58,"n_cols":23,"rows_kept":58,"cut":false,"hidden":false,"merged":[[57,10,57,11],[32,10,32,11],[36,4,36,8],[36,10,36,11],[16,4,16,8],[24,10,24,11],[20,4,20,8],[28,4,28,8],[26,4,26,8],[40,4,40,13],[42,10,42,11],[44,10,44,11],[46,10,46,11],[48,10,48,11],[50,10,50,11],[52,10,52,11],[6,7,6,10],[1,1,1,16],[4,7,4,10],[8,7,8,10],[10,7,10,10],[14,4,14,13],[34,10,34,11],[20,10,20,11],[22,10,22,11],[18,10,18,11],[22,4,22,8],[30,4,30,8],[24,4,24,8],[18,4,18,8],[34,4,34,8],[16,10,16,11],[52,3,53,9],[42,3,43,9],[44,3,45,9],[46,3,47,9],[48,3,49,9],[50,3,51,9]]}
[]
[null,"Ипотечный калькулятор "]
[]
[]
[null,null,null,null,"ФИО заемщика",null,null,"Петров П.П."]
[]
[null,null,null,null,"Тип продукта",null,null,"Переезд-межгород"]
[]
[null,null,null,null,"Договор №",null,null,"21/514-65/754св"]
[]
[null,null,null,null,"Дата договора",null,null,"2005-07-01"]
[]
[]
[]
[null,null,null,null,"Расчетные параметры"]
[]
[null,null,null,null,"Сумма кредита",null,null,null,null,null,2000000,null,", руб."]
[]
[null,null,null,null,"Стоимость предмета ипотеки",null,null,null,null,null,3000000,null,", руб."]
[]
[null,null,null,null,"Дата выдачи кредита (займа)",null,null,null,null,null,"2005-07-05"]
[]
[null,null,null,null,"Срок кредита на момент выдачи",null,null,null,null,null,24,null,", месяцев",null,null,null,null,null,null," "]
[]
[null,null,null,null,"Регион",null,null,null,null,null,"Алтайский край",null,null,null,null,null,null,null,null," "]
[]
[null,null,null,null,"Наличие личного страхования"]
[]
[null,null,null,null,"Новое жилье - новостройка"]
[]
[null,null,null,null,"Оформление послед. ипотеки"]
[]
[null,null,null,null,"Стандарт выдачи",null,null,null,null,null,"Стандарт 1-31 (коп)"]
[]
[null,null,null,null,"Ставка по кредиту",null,null,null,null,null,0.14700000000000002]
[]
[null,null,null,null,"К/З на момент выдачи",null,null,null,null,null,0.67]
[]
[]
[]
[null,null,null,null,"Расчет ПСК"]
[]
[null,null,null,"Единоразовые платежи заемщика при преоставлении кредита (займа), в том числе в пользу третьих лиц, в рублях",null,null,null,null,null,null,0]
[]
[null,null,null,"Ежегодные платежи в процессе обслуживания кредита (займа), в том числе в пользу третьих лиц, в рублях",null,null,null,null,null,null,0]
[]
[null,null,null,"Единоразовые платежи заемщика (комиссии) при предоставлении кредита (займа), в том числе в пользу третьих лиц, в % от ОД",null,null,null,null,null,null,0]
[]
[null,null,null,"Ежегодные платежи в процессе обслуживания кредита (займа), в том числе в пользу третьих лиц, в % от ОД",null,null,null,null,null,null,0]
[]
[null,null,null,"Страховой платеж при предоставлении кредита (займа), в % от ОД",null,null,null,null,null,null,0]
[]
[null,null,null,"Страховые платежи в процессе обслуживания кредита (займа), в % от ОД",null,null,null,null,null,null,0]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,null,null,null,"Расчетное значение ТЗ/З",null,null,null,null,null,0.86129999999999995]

{"sheet":"Настройка калькулятора","index":1,"n_rows":27,"n_cols":10,"rows_kept":27,"cut":false,"hidden":true,"merged":[[24,2,24,3],[21,2,21,3],[2,2,2,3],[4,7,5,7],[4,8,5,8],[3,7,3,9],[4,9,5,9],[14,2,14,3],[22,2,22,3],[23,2,23,3]]}
[]
[]
[null,null,"Константы"]
[null,null,300000,"Минимальный размер кредита",null,null,null,"Процентные ставки"]
[null,null,1,"Максимальное значение ТЗ/З",null,null,null,"                                                       К/З\nСрок","До 50% (включительно)","От 51 до 70% (включительно)"]
[null,null,0.8,"Значение ТЗ/З при котором необходимо ипотечное страхование"]
[null,null,0.3,"Минимальное значение К/З",null,null,null,"До 12 мес.(включительно)",0.121,0.1235]
[null,null,0.7,"Максимальное значение К/З",null,null,null,"От 13 до 24 мес.",0.122,0.13250000000000001]
[null,null,6,"Минимальный срок кредита на момент выдачи (мес.)"]
[null,null,1.5,"Коэффициент превышения суммы кредита, установленной для региона"]
[null,null,24,"Максимальный срок кредита на момент выдачи (мес.)"]
[]
[]
[]
[null,null,"Доп. Надбавки"]
[null,null,"Надбавка","Причина надбавки"]
[null,null,7.0000000000000001E-3,"Надбавка за отсутствие личного страхования "]
[null,null,0.01,"Наличие обременения"]
[]
[]
[]
[null,null,"Типы продуктов"]
[null,null,"Переезд-эконом"]
[null,null,"Переезд-комфорт"]
[null,null,"Переезд-межгород"]
[]
[null,null,null," "]

{"sheet":"Расчет графика платежей","index":2,"n_rows":37,"n_cols":17,"rows_kept":37,"cut":false,"hidden":false,"merged":[[8,5,8,7],[10,3,11,3],[10,4,11,4],[1,1,1,4],[4,1,4,4],[2,1,2,4],[3,1,3,4],[10,1,11,1],[10,2,11,2],[8,1,8,4],[10,16,11,16],[10,5,10,6],[10,13,11,13],[10,14,11,14],[10,8,11,8],[10,7,11,7],[10,9,11,9],[10,10,10,11],[0,1,0,4],[7,1,7,4],[6,1,6,4],[5,1,5,4],[0,5,0,7],[1,5,1,7],[2,5,2,7],[3,5,3,7],[4,5,4,7],[6,5,6,7],[7,5,7,7],[0,11,1,16],[2,13,2,16],[3,13,3,16],[2,9,3,9],[4,9,6,9],[6,13,6,16],[4,13,4,16],[7,13,7,16],[8,13,8,16],[2,11,2,12],[3,11,3,12],[4,11,4,12],[6,11,6,12],[7,11,7,12],[8,11,8,12]]}
[null,"ФИО заемщика",null,null,null,{"$f":"FIO","v":"Петров П.П."},null,null,null,null,null,"Плановые платежи, производимые заемщиком за весь срок кредитования"]
[null,"Тип продукта",null,null,null,{"$f":"ProductType","v":"Переезд-межгород"}]
[null,"Договор №",null,null,null,{"$f":"AgreementNo","v":"21/514-65/754св"},null,null,null,"ПСК",null,{"$f":"F5","v":2000000},null,"погашение кредита"]
[null,"Дата договора",null,null,null,{"$f":"AgreementDate","v":"2005-07-01"},null,null,null,null,null,{"$f":"SUM(N14:N37)+SUM(A14:A37)","v":583972.5299999998},null,"проценты  по кредиту"]
[null,"Сумма кредита (займа)",null,null,null,{"$f":"CredSumm","v":2000000},null,null,null,{"$f":"XIRR(M13:M37,P13:P37,2)","v":0.13766028359532356},null,{"$f":"Payment1+Proc1*CredSumm+Proc3*CredSumm*1.1","v":0},null,"платежи при предоставлении кредита"]
[null,"Стандарт выдачи",null,null,null,{"$f":"GiveStandart","v":"Стандарт 1-31 (коп)"}]
[null,"Дата выдачи кредита",null,null,null,{"$f":"GiveDate","v":"2005-07-05"},null,null,null,null,null,{"$f":"M26","v":0},null,"платежи в процессе обслуживания кредита"]
[null,"Процентная ставка по кредиту",null,null,null,{"$f":"CONCATENATE(InterestPercent*100,\"% годовых\")","v":"14,7% годовых"},null,null,null,null,null,{"$f":"L3+L4+L5+L7","v":2583972.5299999998},null,"всего платежей по кредиту"]
[null,"Срок кредита (займа)",null,null,null,{"$f":"CONCATENATE(Credit_Period,\" мес.\")","v":"24 мес."}]
[]
[null,"№ пп","Последняя дата процентного периода","Фактическая дата платежа","Сумма фактического платежа, руб.","Задолженность на начало процентного периода",null,"Кол-во дней начисления %","Ставка, %","Проценты начисленные на остаток долга","Задолженность на конец процентного периода",null,null,"Платеж в счет погашения задолженности по  процентам, руб.","Платеж в счет погашения ОД, руб.",null,"Возврат"]
[null,null,null,null,null,"Остаток основного долга по кредиту (займу)","Задолженность по неоплаченным процентам, руб",null,null,null,"Остаток основного долга по кредиту (займу)","Задолженность по неоплаченным процентам, руб"]
[null,"[1]","[2]","[3]","[4]","[5]","[6]","[7]","[8]","[9]","[10]","[11]",-2000000,"[12]","[13]",38538,"[14]"]
[{"$f":"IF(B14=Credit_Period,L14,0)","v":0},1,"2005-07-31",null,null,2000000,0,{"$f":"IF(E14>=F5+ROUND((DAY(IF(D14=\"\",C14,D14))-DAY(F7))*F5*I14/(DATE(YEAR(C14)+1,1,1)-DATE(YEAR(C14),1,1)),2),DAY(IF(D14=\"\",C14,D14))-DAY(F7),DAY(C14)-DAY(F7))","v":26},0.14700000000000002,{"$f":"IF(E14>=F5+ROUND((DAY(IF(D14=\"\",C14,D14))-DAY(F7))*F5*I14/(DATE(YEAR(C14)+1,1,1)-DATE(YEAR(C14),1,1)),2),ROUND((DAY(IF(D14=\"\",C14,D14))-DAY(F7))*F5*I14/(DATE(YEAR(C14)+1,1,1)-DATE(YEAR(C14),1,1)),2),ROUND((F5)*(DAY(C14)-DAY(F7))*I14/(DATE(YEAR(C14)+1,1,1)-DATE(YEAR(C14),1,1)),2))","v":20942.47},{"$f":"(F5-O14)","v":2000000},{"$f":"J14-N14","v":20942.47},{"$f":"Payment1+Proc1*CredSumm+Proc3*CredSumm*1.1+IF(B14=Credit_Period,L14+K14,0)+N14+O14","v":0},{"$f":"IF(E14>=J14,J14,E14)","v":0},{"$f":"IF(IF(E14>=N14,E14-N14,0)>F5,F5,IF(E14>N14,E14-N14,0))","v":0},{"$f":"IF((Payment1+Proc1*CredSumm+Proc3*CredSumm*1.1+IF(B14=Credit_Period,L14+K14,0)+N14+O14)<>0,C14,0)","v":0},{"$f":"IF(E14>=J14+F5,E14-(J14+F5),0)","v":0}]
[0,2,{"$f":"IF(B15<=GiveDate,DATE(YEAR(C14),MONTH(C14)+2,1)-1,\"\")","v":"2005-08-31"},null,null,{"$f":"K14","v":2000000},{"$f":"L14","v":20942.47},{"$f":"IF(F15=0,0,IF(E15>=K14+L14+ROUND(DAY(IF(D15=\"\",C15,D15))*K14*I15/(DATE(YEAR(C15)+1,1,1)-DATE(YEAR(C15),1,1)),2),DAY(IF(D15=\"\",C15,D15)),DAY(C15)))","v":31},{"$f":"IF(F15=0,0,I14)","v":0.14700000000000002},{"$f":"IF(E15>=K14+L14+ROUND(DAY(IF(D15=\"\",C15,D15))*K14*I15/(DATE(YEAR(C15)+1,1,1)-DATE(YEAR(C15),1,1)),2),ROUND(DAY(IF(D15=\"\",C15,D15))*K14*I15/(DATE(YEAR(C15)+1,1,1)-DATE(YEAR(C15),1,1)),2),ROUND((K14)*DAY(C15)*I15/(DATE(YEAR(C15)+1,1,1)-DATE(YEAR(C15),1,1)),2))","v":24969.86},{"$f":"(K14-O15)","v":2000000},{"$f":"L14+J15-N15","v":45912.33},{"$f":"(Payment2+Proc2*(F15+G15)+Proc4*1.1*(F15+G15))*IF((B15-1)/12=ROUND((B15-1)/12,0),1,0)+IF(B15=Credit_Period,L15+K15,0)+N15+O15","v":0},{"$f":"IF(E15>=L14+J15,L14+J15,E15)","v":0},{"$f":"IF(IF(E15>=N15,E15-N15,0)>K14,K14,IF(E15>N15,E15-N15,0))","v":0},{"$f":"IF((Payment2+Proc2*(F15+G15)+Proc4*1.1*(F15+G15))*IF((B15-1)/12=ROUND((B15-1)/12,0),1,0)+IF(B15=Credit_Period,L15+K15,0)+N15+O15<>0,C15,0)","v":0},{"$f":"IF(E15>=J15+K14+L14,E15-(J15+K14+L14),0)","v":0}]
[0,3,"2005-09-30",null,null,2000000,45912.33,30,0.14700000000000002,24164.38,2000000,70076.710000000006,0,0,0,0,0]
[0,4,"2005-10-31",null,null,2000000,70076.710000000006,31,0.14700000000000002,24969.86,2000000,95046.57,0,0,0,0,0]
[0,5,"2005-11-30",null,null,2000000,95046.57,30,0.14700000000000002,24164.38,2000000,119210.95000000001,0,0,0,0,0]
[0,6,"2005-12-31",null,null,2000000,119210.95000000001,31,0.14700000000000002,24969.86,2000000,144180.81,0,0,0,0,0]
[0,7,"2006-01-31",null,null,2000000,144180.81,31,0.14700000000000002,24969.86,2000000,169150.66999999998,0,0,0,0,0]
[0,8,"2006-02-28",null,null,2000000,169150.66999999998,28,0.14700000000000002,22553.42,2000000,191704.08999999997,0,0,0,0,0]
[0,9,"2006-03-31",null,null,2000000,191704.08999999997,31,0.14700000000000002,24969.86,2000000,216673.94999999995,0,0,0,0,0]
[0,10,"2006-04-30",null,null,2000000,216673.94999999995,30,0.14700000000000002,24164.38,2000000,240838.32999999996,0,0,0,0,0]
[0,11,"2006-05-31",null,null,2000000,240838.32999999996,31,0.14700000000000002,24969.86,2000000,265808.18999999994,0,0,0,0,0]
[0,12,"2006-06-30",null,null,2000000,265808.18999999994,30,0.14700000000000002,24164.38,2000000,289972.56999999995,0,0,0,0,0]
[0,13,"2006-07-31",null,null,2000000,289972.56999999995,31,0.14700000000000002,24969.86,2000000,314942.42999999993,0,0,0,0,0]
[0,14,"2006-08-31",null,null,2000000,314942.42999999993,31,0.14700000000000002,24969.86,2000000,339912.28999999992,0,0,0,0,0]
[0,15,"2006-09-30",null,null,2000000,339912.28999999992,30,0.14700000000000002,24164.38,2000000,364076.66999999993,0,0,0,0,0]
[0,16,"2006-10-31",null,null,2000000,364076.66999999993,31,0.14700000000000002,24969.86,2000000,389046.52999999991,0,0,0,0,0]
[0,17,"2006-11-30",null,null,2000000,389046.52999999991,30,0.14700000000000002,24164.38,2000000,413210.90999999992,0,0,0,0,0]
[0,18,"2006-12-31",null,null,2000000,413210.90999999992,31,0.14700000000000002,24969.86,2000000,438180.7699999999,0,0,0,0,0]
[0,19,"2007-01-31",null,null,2000000,438180.7699999999,31,0.14700000000000002,24969.86,2000000,463150.62999999989,0,0,0,0,0]
[0,20,"2007-02-28",null,null,2000000,463150.62999999989,28,0.14700000000000002,22553.42,2000000,485704.04999999987,0,0,0,0,0]
[0,21,"2007-03-31",null,null,2000000,485704.04999999987,31,0.14700000000000002,24969.86,2000000,510673.90999999986,0,0,0,0,0]
[0,22,"2007-04-30",null,null,2000000,510673.90999999986,30,0.14700000000000002,24164.38,2000000,534838.2899999998,0,0,0,0,0]
[0,23,"2007-05-31",null,null,2000000,534838.2899999998,31,0.14700000000000002,24969.86,2000000,559808.14999999979,0,0,0,0,0]
[583972.5299999998,24,"2007-06-30",null,null,2000000,559808.14999999979,30,0.14700000000000002,24164.38,2000000,583972.5299999998,2583972.5299999998,0,0,39263,0]

{"sheet":"Регионы","index":3,"n_rows":68,"n_cols":87,"rows_kept":68,"cut":false,"hidden":true,"merged":[]}
[null,"к/з min, % ","к/з max, % (включительно)","Алтайский край","Амурская область","Архангельская область","Астраханская область","Белгородская область","Брянская область","Владимирская область","Волгоградская область","Вологодская область","Воронежская область","г. Владивосток","г. Сочи","г.Москва","г.Санкт-Петербург","Еврейская автономная область","Забайкальский край","Ивановская область","Иркутская область","Кабардино-Балкарская Республика","Калининградская область","Калужская область","Камчатский край","Карачаево-Черкесская Республика","Кемеровская область","Кировская область","Костромская область","Краснодарский край","Красноярский край","Курганская область","Курская область","Ленинградская область","Липецкая область","Магаданская область","Московская область","Мурманская область","Нижегородская область","Новгородская область","Новосибирская область","Омская область","Оренбургская область","Орловская область","Пензенская область","Пермский край","Приморский край (за исключением г. Владивосток)","Псковская область","Республика Адыгея","Республика Алтай","Республика Башкортостан","Республика Бурятия","Республика Дагестан","Республика Ингушетия","Республика Калмыкия","Республика Карелия","Республика Коми","Республика Марий Эл","Республика Мордовия","Республика Саха (Якутия)","Республика Северная Осетия - Алания","Республика Татарстан","Республика Тыва","Республика Хакасия","Ростовская область","Рязанская область","Самарская область","Саратовская область","Сахалинская область","Свердловская область","Смоленская область","Ставропольский край","Тамбовская область","Тверская область","Томская область","Тульская область","Тюменская область","Удмуртская Республика","Ульяновская область","Хабаровский край","Ханты-Мансийский автономный округ - Югра","Челябинская область","Чеченская Республика","Чувашская Республика","Чукотский автономный округ","Ямало-Ненецкий автономный округ","Ярославская область"]
[null,30,50,2.7,4.2,3.2,2.4,3.2,2.6,3.1,3,2.7,2.8,5,6.1,10,5.7,2.6,2.8,2.7,3.5,2.6,3,4.2,3.2,1.9,2.9,2.6,2.8,3,3.2,2.6,2.1,3.9,2.8,2.6,7.1,2.6,3.2,2.9,3.1,2.6,2.8,2.4,2.7,3.1,3.1,2.7,2.1,2.8,3.5,2.7,2.7,1.7,2.1,3.1,3.9,2.7,2.7,4.0999999999999996,1.9,3.5,3.1,2.9,3.9,2.9,3,2.1,4.2,4.0999999999999996,3,2.6,2.4,3.1,3,2.9,3.1,2.8,2.6,4.0999999999999996,5,2.7,1.8,2.8,2.6,3.9,3.1]
[null,50,60,2.4,3.8,2.9,2.1,2.9,2.2999999999999998,2.8,2.7,2.4,2.5,4.5,5.5,9,5.0999999999999996,2.2999999999999998,2.5,2.4,3.1,2.2999999999999998,2.7,3.8,2.9,1.7,2.6,2.2999999999999998,2.5,2.7,2.9,2.2999999999999998,1.9,3.5,2.5,2.2999999999999998,6.4,2.2999999999999998,2.9,2.6,2.8,2.2999999999999998,2.5,2.1,2.4,2.8,2.8,2.4,1.9,2.5,3.1,2.4,2.4,1.5,1.9,2.8,3.5,2.4,2.4,3.7,1.7,3.1,2.8,2.6,3.5,2.6,2.7,1.9,3.8,3.7,2.7,2.2999999999999998,2.1,2.8,2.7,2.6,2.8,2.5,2.2999999999999998,3.7,4.5,2.4,1.6,2.5,2.2999999999999998,3.5,2.8]
[null,60,70,2.1,3.4,2.6,1.9,2.6,2,2.5,2.4,2.1,2.2000000000000002,4,4.9000000000000004,8,4.5999999999999996,2,2.2000000000000002,2.1,2.8,2,2.4,3.4,2.6,1.5,2.2999999999999998,2,2.2000000000000002,2.4,2.6,2,1.7,3.1,2.2000000000000002,2,5.8,2,2.6,2.2999999999999998,2.5,2,2.2000000000000002,1.9,2.1,2.5,2.5,2.1,1.7,2.2000000000000002,2.8,2.1,2.1,1.3,1.7,2.5,3.1,2.1,2.1,3.3,1.5,2.8,2.5,2.2999999999999998,3.1,2.2999999999999998,2.4,1.7,3.4,3.3,2.4,2,1.9,2.5,2.4,2.2999999999999998,2.5,2.2000000000000002,2,3.3,4,2.1,1.4,2.2000000000000002,2,3.1,2.5]
[]
[]
[]
[null,null,null,null,null,null,null," "]
[null,"к/з min, % ","к/з max, % (включительно)","г.Москва","Все регионы (за исключением г. Москвы)"]
[null,30,50,10,7.1]
[null,50,60,9,6.4]
[null,60,70,8,5.8]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"                                                                                                                                                                              "]