Планирайте изплащанията на заем с формули на 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 долара върху нашата месечна обща сума от 1, 161.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 периода заемът ни се изплаща.