Microsoft Excel to jedna z najbardziej kompletnych opcji pracy z danymi w znacznie bardziej scentralizowany sposób z każdego z nich. Excel posiada setki funkcji i formuł przeznaczonych do znacznie pełniejszej pracy a jedną z nich jest wykorzystanie formuł do określenia tabeli spłat kredytu, pamiętaj, że amortyzacja polega na stopniowym zmniejszaniu zadłużenia lub pożyczki z okresowymi płatnościami, tabeli spłat w programie Excel poda nam globalne szczegóły każdej płatności, dopóki nie spłacimy kredytu w całości.
Wymagania dotyczące tabeli amortyzacjiAby poprawnie utworzyć tabelę amortyzacji w Excelu, wymagane jest:
- Kwota kredytu: musimy znać dokładną kwotę kredytu, jest to wartość, którą ostatecznie udziela nam bank.
- Stopa procentowa: kluczowym aspektem jest znajomość stopy procentowej, jaką nalicza nam instytucja finansowa, z reguły oprocentowanie jest odliczane co roku.
- Liczba płatności: znajomość liczby płatności pozwala nam wiedzieć, jak długo będziemy powiązani z instytucją finansową, płatności te są reprezentowane w okresach miesięcznych, takich jak 12, 24, 36, 48 itd.
DaneW naszym przypadku obsługujemy następujące dane:
- Kwota pożyczki: 380 000 zł
- Miesiące: 12
- Oprocentowanie miesięczne: 3%
POWIĘKSZAĆ
Dzięki tym danym zobaczymy, jak szczegółowo ustalić stałą opłatę za tabelę amortyzacji Excela w tekście i wideo.
1. Oblicz stałą opłatę Excel
Krok 1
Pierwszym krokiem do wykonania będzie obliczenie stałej opłaty, którą musimy zapłacić, w tym celu przechodzimy do komórki „Stała opłata miesięczna” i użyjemy formuły „Płatność” w następujący sposób:
= ZAPŁATA (B4; B5; B3)Krok 2
Zdefiniowano w nim następujące wartości:
(odsetki miesięczne; liczba miesięcy; kwota pożyczki)Krok 3
Domyślny wynik da liczbę ujemną:
POWIĘKSZAĆ
Krok 4
Aby tabela amortyzacji działała poprawnie musimy przeliczyć wartość ujemną na dodatnią, do tego wpisujemy znak - przed funkcją „płatność”:
= -PŁATNOŚĆ (B4; B5; B3)Krok 5
Teraz liczba będzie dodatnia:
POWIĘKSZAĆ
2. Skonfiguruj tabelę amortyzacji Excel
Krok 1
Nadszedł czas, aby zarządzać tabelą, aby szczegółowo poznać wartości do zapłaty i zebrania, najpierw wpisać miesiące, ponieważ w tym przypadku jest 12, wpisujemy od okresu 0 do 12 (w okresie zero wprowadzić wypłatę kredytu).
W komórce Saldo końcowe możemy odwołać się do komórki, w której została wpisana kwota pożyczki:
POWIĘKSZAĆ
Krok 2
Teraz komórka F10, w której wprowadziliśmy kwotę pożyczki, znajduje się w komórce B11 związanej z początkowym saldem kredytu:
POWIĘKSZAĆ
Krok 3
W sekcji „Opłata stała” zdefiniowaliśmy już stałą wartość miesięczną, wartość ta nie zmieni się w całej tej sekcji, w komórce C11 wpisujemy odniesienie do komórki, w której została zdefiniowana stała opłata (B6), ta komórka musi być przekonwertowana w referencję bezwzględną, aby jej wartość nie zmieniała się w miarę przeciągania komórki w dół, podczas wprowadzania referencji wciskamy klawisz F4 i zarówno obok kolumny jak i komórki zobaczymy znak $:
= $B $6
POWIĘKSZAĆ
Krok 4
Teraz obliczamy miesięczne odsetki, w tym celu pomnożymy saldo początkowe przez stopę procentową naliczaną według następującego wzoru w tym przykładzie:
= B11 * $ B $ 4Notatkakomórka B4 została pozostawiona bezwzględna, ponieważ jest to stała wartość.
POWIĘKSZAĆ
Krok 5
Teraz pora na obliczenie wpłaconego kapitału, czyli różnicy między opłatą stałą a płaconymi miesięcznymi odsetkami, użyjemy następującego wzoru:
= C11-D11Krok 6
Rezultatem tego jest to, co zostanie pomniejszone z początkowego długu:
POWIĘKSZAĆ
Krok 7
Aby określić saldo końcowe, weźmiemy saldo początkowe minus kapitał wpłacony:
= B11-E11
POWIĘKSZAĆ
Krok 8
Na koniec po prostu wybieramy komórki tabeli amortyzacji i przeciągamy je z prawego dolnego rogu do ostatniego zdefiniowanego okresu, aby wyświetlić wartości do zapłaty:
POWIĘKSZAĆ
Dzięki tej prostej metodzie możemy stworzyć przykładową tabelę amortyzacji w Excelu i dzięki temu mieć precyzyjną kontrolę nad wartościami, które musimy płacić miesiąc po miesiącu i mieć pewność, czy uzgodnione warunki są spełnione.