Съдържание
- Разбиране на вашата ипотека
- Изчислете месечното плащане
- Изчислете годишната лихва
- Определяне на дължината на заем
- Премахване на Заема
- Изчисляване на заем в Excel
- Амортизация на Заема
- Създаване на графика за заем
Погасяването на заем е актът за изплащане на парите, получени преди от заемодателя, обикновено чрез поредица от периодични плащания, които включват главница плюс лихва. Знаете ли, че можете да използвате софтуерната програма Excel за изчисляване на изплащането на вашия заем?
Тази статия е стъпка по стъпка ръководство за настройка на изчисления за заем.
Ключови заведения
- Използвайте Excel, за да получите справка за вашата ипотека чрез определяне на месечното си плащане, лихвения процент и вашия график на заема. Можете да разгледате по-задълбочено разбивката на заем с excel и да създадете план за погасяване, който работи за вас. Има налични изчисления за всяка стъпка, която можете да настроите, за да отговори на вашите специфични нужди. Разрушаването и разглеждането на вашия заем стъпка по стъпка може да направи процеса на погасяване да се почувства по-малко зашеметяващ и по-управляем.
Разбиране на вашата ипотека
Използвайки Excel, можете да получите по-добро разбиране на вашата ипотека в три прости стъпки. Първата стъпка определя месечното плащане. Втората стъпка изчислява лихвения процент, а третата стъпка определя схемата на заема.
Можете да създадете таблица в Excel, която ще ви каже лихвения процент, изчислението на заема за продължителността на заема, разлагането на заема, амортизацията и месечното плащане.
Изчислете месечното плащане
Първо, ето как да изчислите месечното плащане за ипотека. Използвайки годишния лихвен процент, главницата и продължителността, можем да определим сумата, която трябва да се връща месечно.
Формулата, както е показана на екрана по-горе, се записва по следния начин:
= -PMT (скорост; дължина; настояща_стойност;;)
Знакът минус пред PMT е необходим, тъй като формулата връща отрицателно число. Първите три аргумента са процентът на кредита, продължителността на заема (брой периоди) и заетата главница. Последните два аргумента са незадължителни, остатъчната стойност е нула; заплаща се предварително (за един) или в края (за нула), също не е задължително.
Формулата на Excel, използвана за изчисляване на месечното плащане на заема, е:
= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Обяснение: За ставката използваме месечната ставка (период на ставката), след това изчисляваме броя на периодите (120 за 10 години, умножени по 12 месеца) и накрая посочваме заетата главница. Месечното ни плащане ще бъде 1161, 88 долара за 10 години.
Изчислете годишната лихва
Видяхме как да настроите изчисляването на месечно плащане за ипотека. Но може да искаме да определим максимално месечно плащане, което можем да си позволим, което също показва броя на годините, през които би трябвало да изплатим заема. Поради тази причина бихме искали да знаем съответния годишен лихвен процент.
Както е показано на екрана по-горе, първо изчисляваме процентната ставка (месечна, в нашия случай), а след това и годишната ставка. Използваната формула ще бъде RATE, както е показано на екрана по-горе. Пише се както следва:
= Процент (БрПер; Пл; настояща_стойност;;)
Първите три аргумента са продължителността на заема (брой периоди), месечното плащане за погасяване на заема и заетата главница. Последните три аргумента са незадължителни, а остатъчната стойност по подразбиране е нула; терминният аргумент за управление на падежа предварително (за един) или в края (за нула) също е незадължителен. И накрая, аргументът за оценка е незадължителен, но може да даде първоначална оценка на процента.
Формулата на Excel, използвана за изчисляване на лихвения процент е:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Забележка: на съответните данни в месечното плащане трябва да се посочи отрицателен знак. Ето защо пред формулата има знак минус. Периодът на ставката е 0, 294%.
Използваме формулата = (1 + B5) е 12-1 ^ = (1 + 0, 294%) ^ 12-1, за да получим годишната ставка на нашия заем, която е 3, 58%. С други думи, за да заемаме 120 000 долара за 13 години, за да плащаме 960 долара месечно, трябва да договорим заем при годишна максимална ставка 3, 58%.
Използването на Excel е чудесен начин за проследяване на това, което дължите и изготвяне на график за погасяване, който свежда до минимум всички такси, които може да се окажат дължими.
Определяне на дължината на заем
Сега ще видим как да определим продължителността на заема, когато знаете годишния лихвен процент, заетата главница и месечното плащане, което трябва да бъде изплатено. С други думи, колко време ще трябва да изплатим ипотека от 120 000 долара със ставка 3, 10% и месечно плащане от 1100 долара?
Формулата, която ще използваме, е NPER, както е показано на екрана по-горе, и е написана, както следва:
= БрПер (скорост; Пл; настояща_стойност;;)
Първите три аргумента са годишната ставка на заема, месечното плащане, необходимо за погасяване на заема, и главницата, заета. Последните два аргумента са незадължителни, остатъчната стойност е нула. Терминът аргумент, платим предварително (за един) или в края (за нула), също е незадължителен.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)
Забележка: на съответните данни в месечното плащане трябва да се посочи отрицателен знак. Ето защо имаме формула минус преди формулата. Продължителността на възстановяването е 127, 97 периода (в нашия случай месеци).
Ще използваме формулата = B5 / 12 = 127, 97 / 12 за броя години, за да завършим изплащането на кредита. С други думи, за да вземем заем от 120 000 долара, с годишна лихва 3, 10% и да плащаме 1100 долара месечно, трябва да изплащаме падежи за 128 месеца или 10 години и 8 месеца.
Премахване на Заема
Плащането по заем се състои от главница и лихва. Лихвата се изчислява за всеки период - например месечните плащания за 10 години ще ни дадат 120 периода.
Таблицата по-горе показва разбивка на заем (общ период равен на 120) с помощта на формулите PPMT и IPMT. Аргументите на двете формули са еднакви и се разбиват, както следва:
= -PPMT (скорост; num_period; дължина; основната;;)
Аргументите са същите като при вече видяната формула на PMT, с изключение на "num_period", която се добавя, за да покаже периода, през който да се раздели заемът предвид главницата и лихвата. Ето пример:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120 000)
Резултатът е показан на екрана по-горе „Разлагане на заем“ през анализирания период, който е „един;“ тоест първият период или първият месец. Плащаме 1161, 88 долара, разбити на главница 856, 20 и лихва от 305, 68 долара.
Изчисляване на заем в Excel
Възможно е също да се изчисли изплащането на главницата и лихвата за няколко периода, като първите 12 месеца или първите 15 месеца.
= -CUMPRINC (скорост; дължина; основната; start_date; крайна_дата тип)
Ние намираме аргументите, процента, дължината, главницата и термина (които са задължителни), които вече видяхме в първата част с формулата PMT. Но тук имаме нужда и от аргументите "start_date" и "end_date". "Start_date" указва началото на периода, който трябва да се анализира, а "end_date" указва края на периода, който трябва да се анализира.
Ето пример:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; В4 * 12; В3; 1; 12; 0)
Резултатът е показан на екрана "Cumul 1-ва година", така че анализираните периоди варират от един до 12 от първия период (първи месец) до дванадесетия (12-ия месец). За една година щяхме да платим 10 419, 55 долара главница и 3, 522, 99 долара лихва.
Амортизация на Заема
Предходните формули ни позволяват да създадем своя график за период, да знаем колко ще плащаме месечно главница и лихва и да знаем колко остава да платим.
Създаване на графика за заем
За да създадем заемна схема, ще използваме различните формули, разгледани по-горе и ще ги разширим по броя на периодите.
В първата колона за период въведете "1" като първи период и след това плъзнете клетката надолу. В нашия случай се нуждаем от 120 периода, тъй като 10-годишното плащане на заем, умножено по 12 месеца, е 120.
Втората колона е месечната сума, която трябва да плащаме всеки месец - което е постоянно за целия график на заема. За да изчислите сумата, вмъкнете следната формула в клетката от първия ни период:
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Третата колона е главницата, която ще се изплаща месечно. Например, за 40-ия период, ние ще изплатим главница 945, 51 долара върху нашата месечна обща сума от 1161, 88 долара.
За да изчислим основната сума, използвана по следния начин, използваме следната формула:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
Четвъртата колона е лихвата, за която използваме формулата, за да изчислим изплатената главница на месечната си сума, за да открием колко лихва трябва да бъде платена:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
Петата колона съдържа оставената сума за плащане. Например след 40-тото плащане ще трябва да платим 83 994, 69 долара срещу 120 000 долара.
Формулата е следната:
= $ B $ 3 + CUMPRINC (ТР; $ B $ 4 * 12; $ B $ 3; 1; A18 0)
Формулата използва комбинация от главница за период преди клетката, съдържаща заетата главница. Този период започва да се променя, когато копираме и плъзнем клетката надолу. Таблицата по-долу показва, че в края на 120 периода заемът ни се изплаща.
