Създаването на амортизационен график на заема в Excel може да бъде ценно умение, което ви позволява да визуализирате и управлявате ефективно изплащането на заема си. Амортизационният график е таблица, която описва всяко периодично плащане по амортизационен заем (обикновено ипотека или заем за кола). Той разделя всяко плащане на компоненти лихва и главница, показвайки оставащото салдо след всяко плащане. Нека се потопим в ръководство стъпка по стъпка за създаване на такъв график в Excel.

Какво е амортизационен график?

Създайте амортизационен план в Excel

Създайте амортизационен график за променлив брой периоди

Създайте амортизационен план с допълнителни плащания

Създайте амортизационен график (с допълнителни плащания) с помощта на шаблон на Excel

Какво е амортизационен график?

Амортизационният график е подробна таблица, използвана при изчисленията на заема, която показва процеса на изплащане на заема във времето. Амортизационните графици обикновено се използват за заеми с фиксиран лихвен процент като ипотеки, заеми за автомобили и лични заеми, където сумата на плащането остава постоянна през целия срок на заема, но съотношението на плащане към лихва спрямо главница се променя с времето.

За да създадете график за амортизация на заема в Excel, наистина вградените функции PMT, PPMT и IPMT са от решаващо значение. Нека разберем какво прави всяка функция:

• PMT функция: Тази функция се използва за изчисляване на общото плащане за период за заем въз основа на постоянни плащания и постоянен лихвен процент.
• IPMT функция: Тази функция изчислява лихвената част от плащането за даден период.
• Функция PPMT: Тази функция се използва за изчисляване на основната част от плащане за определен период.

Като използвате тези функции в Excel, можете да създадете подробен амортизационен график, който показва компонентите на лихвата и главницата на всяко плащане, заедно с оставащото салдо по заема след всяко плащане.

Създайте амортизационен план в Excel

В този раздел ще представим два различни метода за създаване на амортизационен график в Excel. Тези методи отговарят на различни потребителски предпочитания и нива на умения, като гарантират, че всеки, независимо от уменията си с Excel, може успешно да състави подробен и точен амортизационен график за своя заем.

Формулите предлагат по-задълбочено разбиране на основните изчисления и осигуряват гъвкавост за персонализиране на графика според конкретни изисквания. Този подход е идеален за тези, които желаят да имат практически опит и ясна представа за това как всяко плащане се разбива на компоненти на главница и лихва. Сега нека разбием стъпка по стъпка процеса на създаване на амортизационен график в Excel:

⭐️ Стъпка 1: Настройте информацията за заема и амортизационната таблица

1. Въведете относителната информация за заема, като годишен лихвен процент, срок на заема в години, брой плащания на година и сума на заема в клетките, както е показано на следната екранна снимка:
   
2. След това създайте амортизационна таблица в Excel с посочените етикети, като Период, Плащане, Лихва, Главница, Остатъчен баланс в клетки A7:E7.
3. В колоната Период въведете номерата на периодите. За този пример общият брой плащания е 24 месеца (2 години), така че ще въведете числа от 1 до 24 в колоната Период. Вижте екранна снимка:
   
4. След като настроите таблицата с етикетите и номерата на периодите, можете да продължите да въвеждате формули и стойности за колоните Плащане, Лихва, Главница и Баланс въз основа на спецификата на вашия заем.

⭐️ Стъпка 2: Изчислете общата сума на плащането, като използвате функцията PMT

Синтаксисът на PMT е:

Въведете следната формула в клетка B7 и след това плъзнете манипулатора за попълване надолу, за да попълните тази формула в други клетки, и ще видите постоянна сума на плащане за всички периоди. Вижте екранна снимка:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Стъпка 3: Изчислете лихвата с помощта на функцията IPMT

В тази стъпка ще изчислите лихвата за всеки период на плащане, като използвате функцията IPMT на Excel.

Въведете следната формула в клетка C7 и след това плъзнете манипулатора за попълване надолу по колоната, за да попълните тази формула и да получите лихвата за всеки период.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Стъпка 4: Изчислете главницата с помощта на функцията PPMT

След изчисляване на лихвата за всеки период, следващата стъпка в създаването на амортизационен график е да се изчисли основната част от всяко плащане. Това става с помощта на функцията PPMT, която е предназначена да определи главницата на плащането за определен период, въз основа на постоянни плащания и постоянен лихвен процент.

Синтаксисът на IPMT е:

Синтаксисът и параметрите за формулата PPMT са идентични с тези, използвани в обсъжданата по-рано формула IPMT.

Въведете следната формула в клетка D7 и след това плъзнете манипулатора за попълване надолу по колоната, за да попълните главницата за всеки период. Вижте екранна снимка:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Стъпка 5: Изчислете оставащия баланс

След като изчислите както лихвата, така и главницата на всяко плащане, следващата стъпка във вашия амортизационен график е да изчислите оставащия баланс на заема след всяко плащане. Това е важна част от графика, тъй като показва как салдото по кредита намалява с времето.

1. В първата клетка на колоната на вашия баланс – E7, въведете следната формула, което означава, че оставащият баланс ще бъде първоначалната сума на заема минус главницата на първото плащане:

   =B4-D7

   
2. За втория и всички следващи периоди изчислете оставащото салдо, като извадите основното плащане за текущия период от салдото за предходния период. Моля, приложете следната формула в клетка E8:

   =E7-D8

    Забележка: Препратката към клетката за баланс трябва да е относителна, така че да се актуализира, докато плъзгате формулата надолу.
   
3. И след това плъзнете манипулатора за запълване надолу към колоната. Както можете да видите, всяка клетка автоматично ще се коригира, за да изчисли оставащия баланс въз основа на актуализираните плащания на главницата.
   

⭐️ Стъпка 6: Направете резюме на заема

След като настроите своя подробен амортизационен график, създаването на резюме на заема може да осигури бърз преглед на ключовите аспекти на вашия заем. Това обобщение обикновено включва общата цена на заема, общата платена лихва.

● За изчисляване на общите плащания:

=SUM(B7:B30)

● За да изчислите общата лихва:

=SUM(C7:C30)

⭐️ Резултат:

Сега успешно е създаден опростен, но изчерпателен график за погасяване на заема. виж екранна снимка:

Създайте амортизационен график за променлив брой периоди

В предишния пример създаваме график за погасяване на заема за фиксиран брой плащания. Този подход е идеален за обработка на конкретен заем или ипотека, при които условията не се променят.

Но ако търсите да създадете гъвкав амортизационен график, който може да се използва многократно за заеми с различни периоди, което ви позволява да променяте броя на плащанията, както се изисква за различни сценарии на заем, ще трябва да следвате по-подробен метод.

⭐️ Стъпка 1: Настройте информацията за заема и амортизационната таблица

1. Въведете относителната информация за заема, като годишен лихвен процент, срок на заема в години, брой плащания на година и сума на заема в клетките, както е показано на следната екранна снимка:
   
2. След това създайте амортизационна таблица в Excel с посочените етикети, като Период, Плащане, Лихва, Главница, Остатъчен баланс в клетки A7:E7.
3. В колоната „Период“ въведете най-големия брой плащания, които можете да обмислите за всеки заем, например попълнете числата, вариращи от 1 до 360. Това може да покрие стандартен 30-годишен заем, ако правите месечни плащания.
   

⭐️ Стъпка 2: Променете формулите за плащане, лихва и главница с функцията IF

Въведете следните формули в съответните клетки и след това плъзнете манипулатора за попълване, за да разширите тези формули до максималния брой периоди на плащане, който сте задали.

● Формула на плащане:

Обикновено използвате функцията PMT за изчисляване на плащането. За да включите оператор IF, синтактичната формула е:

Така че формулите са следните:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Лихвена формула:

Синтаксичната формула е:

Така че формулите са следните:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Принципна формула:

Синтаксичната формула е:

Така че формулите са следните:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Стъпка 3: Коригирайте формулата за оставащия баланс

За оставащото салдо обикновено можете да извадите главницата от предишното салдо. С оператор IF го променете като:

● Първата балансова клетка: (E7)

=B4-D7

● Втората клетка за баланс: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Стъпка 4: Направете резюме на заема

След като настроите амортизационния график с модифицираните формули, следващата стъпка е да създадете резюме на заема.

● За изчисляване на общите плащания:

=SUM(B7:B366)

● За да изчислите общата лихва:

=SUM(C7:C366)

⭐️ Резултат:

Сега имате изчерпателен и динамичен амортизационен график в Excel, допълнен с подробно резюме на заема. Всеки път, когато коригирате срока на периода на плащане, целият амортизационен график ще се актуализира автоматично, за да отрази тези промени. Вижте демонстрацията по-долу:

Създайте амортизационен план с допълнителни плащания

Чрез извършване на допълнителни плащания извън предвидените, кредитът може да бъде изплатен по-бързо. Амортизационен график, който включва допълнителни плащания, когато е създаден в Excel, демонстрира как тези допълнителни плащания могат да ускорят изплащането на заема и да намалят общата платена лихва. Ето как можете да го настроите:

⭐️ Стъпка 1: Настройте информацията за заема и амортизационната таблица

1. Въведете относителната информация за заема, като годишен лихвен процент, срок на заема в години, брой плащания на година, сума на заема и допълнително плащане в клетките, както е показано на следната екранна снимка:
   
2. След това изчислете планираното плащане.
   В допълнение към клетките за въвеждане, друга предварително дефинирана клетка е необходима за нашите последващи изчисления - сумата на планираното плащане. Това е сумата на редовното плащане по заем, при условие че не се правят допълнителни плащания. Моля, приложете следната формула в клетка B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. След това създайте амортизационна таблица в Excel:
   • Задайте посочените етикети, като Период, Плащане по график, Допълнително плащане, Общо плащане, Лихва, Главница, Остатъчен баланс в клетки A8:G8;
   • В колоната Период въведете най-големия брой плащания, които можете да обмислите за всеки заем. Например, попълнете числата, вариращи от 0 до 360. Това може да покрие стандартен 30-годишен заем, ако правите месечни плащания;
   • За период 0 (ред 9 в нашия случай) извлечете стойността на баланса с тази формула = B4, което съответства на първоначалната сума на кредита. Всички други клетки в този ред трябва да се оставят празни.
   • 

⭐️ Стъпка 2: Създайте формулите за амортизационен график с допълнителни плащания

Въведете следните формули в съответните клетки една по една. За да подобрим обработката на грешки, ние затваряме тази и всички бъдещи формули във функцията IFERROR. Този подход помага да се избегнат множество потенциални грешки, които могат да възникнат, ако някоя от клетките за въвеждане остане празна или съдържа неправилни стойности.

● Изчислете планираното плащане:

Въведете следната формула в клетка B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Изчислете допълнителното плащане:

Въведете следната формула в клетка C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Изчислете общото плащане:

Въведете следната формула в клетка D10:

=IFERROR(B10+C10, "")

● Изчислете главницата:

Въведете следната формула в клетка E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Изчислете лихвата:

Въведете следната формула в клетка F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Изчислете оставащия баланс

Въведете следната формула в клетка G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

След като завършите всяка от формулите, изберете диапазона от клетки B10:G10 и използвайте манипулатора за попълване, за да плъзнете и разширите тези формули надолу през целия набор от периоди на плащане. За всички неизползвани периоди клетките ще показват 0s. Вижте екранна снимка:

⭐️ Стъпка 3: Направете резюме на заема

● Получете планиран брой плащания:

=B2:B3

● Получете действителен брой плащания:

=COUNTIF(D10:D369,">"&0)

● Вземете общо допълнителни плащания:

=SUM(C10:C369)

● Вземете обща лихва:

=SUM(F10:F369)

⭐️ Резултат:

Като следвате тези стъпки, вие създавате динамичен амортизационен график в Excel, който отчита допълнителните плащания.

Създайте амортизационен график с помощта на шаблон на Excel

Създаването на амортизационен график в Excel с помощта на шаблон е лесен и ефективен във времето метод. Excel предоставя вградени шаблони, които автоматично изчисляват лихвата, главницата и оставащото салдо за всяко плащане. Ето как да създадете амортизационен график с помощта на шаблон на Excel:

1. Кликнете досие > НОВ, в полето за търсене въведете амортизационен план, и натиснете Въведете ключ. След това изберете шаблона, който най-добре отговаря на вашите нужди, като щракнете върху него. Например, тук ще избера Обикновен шаблон за кредитен калкулатор. Вижте екранна снимка:
   
2. След като изберете шаблон, щракнете върху Създаване на бутон, за да го отворите като нова работна книга.
3. След това въведете вашите собствени данни за заема, шаблонът трябва автоматично да изчисли и попълни графика въз основа на вашите данни.
4. Най-накрая запазете новата си работна книга за амортизационния график.