Używanie programu Microsoft Excel jako kalkulatora. Kalkulator pożyczki w Excelu i formuły miesięcznych płatności

Excel to uniwersalne narzędzie analityczne i obliczeniowe, z którego często korzystają pożyczkodawcy (banki, inwestorzy itp.) oraz pożyczkobiorcy (przedsiębiorcy, firmy, osoby fizyczne itp.).

Funkcje programu Microsoft Excel pozwalają szybko poruszać się po skomplikowanych formułach, obliczać odsetki, kwoty płatności i nadpłat.

Jak obliczyć ratę kredytu w Excelu

Miesięczne płatności zależą od schematu spłaty kredytu. Istnieją płatności dożywotnie i zróżnicowane:

1. Renta zakłada, że ​​klient płaci co miesiąc tę ​​samą kwotę.
2. W przypadku zróżnicowanego programu spłaty zadłużenia wobec organizacji finansowej odsetki naliczane są od salda kwoty pożyczki. Dlatego miesięczne raty spadną.

Coraz częściej stosuje się rentę: jest ona bardziej opłacalna dla banku i wygodniejsza dla większości klientów.

Obliczanie płatności renty dożywotniej pożyczki w programie Excel

Kwotę miesięcznej płatności renty oblicza się według wzoru:

A = K * S

• A – kwota spłaty kredytu;
• K – współczynnik płatności renty;
• S – kwota pożyczki.

Wzór na współczynnik renty:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• gdzie i to miesięczna stopa procentowa, wynik podzielenia rocznej stopy procentowej przez 12;
• n – okres kredytowania w miesiącach.

Excel ma specjalną funkcję, która oblicza płatności renty. To jest PLT:

Komórki zmieniły kolor na czerwony, a przed liczbami pojawił się znak minus, ponieważ Oddamy te pieniądze bankowi i stracimy je.



Kalkulacja płatności w Excelu według zróżnicowanego schematu spłat

Zróżnicowana metoda płatności zakłada, że:

• kwotę zadłużenia głównego rozkłada się na okresy płatności w równych częściach;
• Odsetki od kredytu naliczane są od salda.

Wzór na obliczenie płatności zróżnicowanej:

DP = NEO / (PP + NEO * PS)

• DP – miesięczna spłata kredytu;
• OBL – saldo kredytu;
• PP – liczba okresów pozostałych do końca okresu spłaty;
• PS – miesięczna stopa procentowa (stopa roczna podzielona przez 12).

Ustalimy harmonogram spłaty poprzedniego kredytu według zróżnicowanego schematu.

Dane wejściowe są takie same:

Stwórzmy harmonogram spłaty kredytu:

Saldo zadłużenia kredytowego: w pierwszym miesiącu równa się całej kwocie: =$B$2. W drugim i kolejnych oblicza się to ze wzoru: =JEŻELI(D10>$B$4;0;E9-G9). Gdzie D10 to numer bieżącego okresu, B4 to okres kredytowania; E9 – saldo kredytu w poprzednim okresie; G9 – kwota zadłużenia głównego w poprzednim okresie.

Oprocentowanie: pomnóż saldo kredytu w bieżącym okresie przez miesięczną stopę procentową podzieloną przez 12 miesięcy: =E9*(3/12 B$).

Płatność kapitału: podziel kwotę całej pożyczki przez wyraz: =IF(D9

Płatność końcowa: wysokość „odsetek” i „długu głównego” w bieżącym okresie: =F8+G8.

Wprowadźmy formuły w odpowiednich kolumnach. Skopiujmy je na całą tabelę.

Porównajmy nadpłatę w przypadku renty dożywotniej i zróżnicowanej spłaty kredytu:

Czerwona liczba to renta (wzięli 100 000 rubli), czarna liczba to metoda zróżnicowana.

Wzór do obliczania odsetek od pożyczki w programie Excel

Obliczmy oprocentowanie kredytu w Excelu i obliczmy efektywną stopę procentową, mając następujące informacje na temat kredytu oferowanego przez bank:

Obliczmy miesięczną stopę procentową i ratę kredytu:

Wypełnijmy tabelę w ten sposób:

Prowizja pobierana jest co miesiąc od całej kwoty. Całkowita spłata kredytu to płatność renty plus prowizja. Kwota główna i kwota odsetek stanowią składniki płatności renty.

Kwota główna = płatność renty – odsetki.

Kwota odsetek = saldo zadłużenia * miesięczna stopa procentowa.

Saldo kapitału = saldo poprzedniego okresu – kwota zadłużenia głównego w poprzednim okresie.

Na podstawie tabeli miesięcznych rat obliczamy efektywną stopę procentową:

• zaciągnął pożyczkę w wysokości 500 000 rubli;
• wrócił do banku - 684 881,67 rubli. (suma wszystkich spłat kredytu);
• nadpłata wyniosła 184 881,67 rubli;
• oprocentowanie – 184 881,67/500 000*100, czyli 37%.
• Nieszkodliwa prowizja w wysokości 1% była dla pożyczkobiorcy bardzo kosztowna.

Efektywne oprocentowanie kredytu bez prowizji wyniesie 13%. Obliczenia przeprowadza się według tego samego schematu.

Kalkulacja całkowitego kosztu pożyczki w Excelu

Zgodnie z ustawą o kredycie konsumenckim obecnie stosuje się nową formułę obliczania całkowitego kosztu kredytu (TCC). LUW określa się jako procent z dokładnością do trzeciego miejsca po przecinku, stosując następujący wzór:

• PSK = i*NBP*100;
• gdzie i jest stopą procentową okresu bazowego;
• NBP to liczba okresów bazowych w roku kalendarzowym.

Weźmy dla przykładu następujące dane dotyczące kredytu:

Aby obliczyć pełny koszt pożyczki, należy sporządzić harmonogram spłat (procedurę opisano powyżej).

Konieczne jest określenie okresu bazowego (BP). Prawo mówi, że jest to standardowy przedział czasowy, który występuje najczęściej w harmonogramie spłat. W przykładzie BP = 28 dni.

Teraz możesz znaleźć bazową stopę procentową okresu:

Mamy wszystkie niezbędne dane - podstawiamy je do wzoru UCS: =B9*B8

Notatka. Aby otrzymać procenty w Excelu, nie trzeba mnożyć przez 100. Wystarczy ustawić format procentowy dla komórki z wynikiem.

PSC według nowej formuły pokrywało się z roczną stopą oprocentowania kredytu.

Zatem do obliczenia płatności renty z tytułu pożyczki stosuje się najprostszą funkcję PMT. Jak widać, metoda zróżnicowanej spłaty jest nieco bardziej skomplikowana.

Zamiast korzystać z kalkulatora, użyj programu Microsoft Excel do wykonywania obliczeń matematycznych!

Możesz wprowadzać proste formuły, aby dodawać, dzielić, mnożyć i odejmować dwie lub więcej wartości liczbowych. Możesz także skorzystać z funkcji AutoSum, aby szybko dodać serię wartości bez konieczności ręcznego wpisywania ich do formuły. Po utworzeniu formuły możesz skopiować ją do sąsiednich komórek — nie ma potrzeby ponownego tworzenia tej samej formuły.

Odejmowanie w Excelu

Mnożenie w Excelu

Podział w Excelu

Proste formuły

Wszystkie wpisy formuł zaczynają się od znaku równości ( = ). Aby utworzyć prostą formułę, wystarczy wpisać znak równości, po którym następują wyliczane wartości liczbowe i odpowiadające im operatory matematyczne: znak plus ( + ) dla dodania, znak minus ( - ) do odejmowania, gwiazdka ( * ) dla mnożenia i ukośnika ( / ) do podziału. Następnie naciśnij Enter, a Excel natychmiast obliczy i wyświetli wynik formuły.

Na przykład, jeśli wprowadzisz formułę w komórce C5 =12,99+16,99 i naciśnij ENTER, Excel obliczy wynik i wyświetli w tej komórce liczbę 29,98.

Formuła wprowadzona w komórce pojawi się na pasku formuły po każdym zaznaczeniu komórki.

Ważny: Chociaż istnieje funkcja kwoty, funkcja Odejmować nieobecny. Zamiast tego użyj w formule operatora minus (-); Na przykład = 8-3 + 2-4 + 12. Możesz także użyć znaku minus, aby przekonwertować liczbę na jej wartość ujemną w funkcjach sumy. Na przykład formuła =SUMA(12, 5, -3, 8, -4) wykorzystuje funkcję sumy do dodawania 12, 5, odejmowania 3, dodawania 8 i odejmowania 4 w tej kolejności.

Korzystanie z Autosumowania

Najprostszym sposobem dodania formuły SUMA do arkusza jest użycie funkcji AutoSum. Wybierz pustą komórkę bezpośrednio nad lub pod zakresem, który chcesz zsumować, a następnie otwórz kartę na Wstążce dom Lub Formuła i wybierz Autosumowanie > Suma. Funkcja AutoSum automatycznie określa zakres sumowania i tworzy formułę. Działa również poziomo, jeśli wybierzesz komórkę po prawej lub lewej stronie zakresu, który ma zostać zsumowany.

Notatka: Funkcja automatycznego sumowania nie działa w przypadku zakresów, które nie sąsiadują ze sobą.

Automatyczne sumowanie w pionie

Powyższy obrazek pokazuje, że funkcja AutoSum automatycznie zidentyfikowała komórki B2:B5 jako zakres do sumowania. Aby potwierdzić, wystarczy nacisnąć ENTER. Jeśli chcesz dodać lub wykluczyć wiele komórek, przytrzymaj klawisz Shift i naciskaj odpowiedni klawisz strzałki, aż podświetlisz żądany zakres. Następnie naciśnij Enter, aby zakończyć zadanie.

Przewodnik po funkcjach IntelliSense: sums (number1; [number2];...) znacznik zmiennoprzecinkowy pod funkcją stanowi przewodnik po technologii IntelliSense. Kliknięcie sumy lub nazwy funkcji spowoduje zmianę niebieskiego hiperłącza do tematu pomocy dla tej funkcji. Kliknięcie na poszczególne elementy funkcji spowoduje podświetlenie odpowiadającej jej jednostki reprezentatywnej we wzorze. W tym przypadku podświetlona zostanie tylko wartość B2:B5, ponieważ w tej formule występuje tylko jedno odniesienie do liczby. Każda funkcja wyświetli tag IntelliSense.

Automatyczne sumowanie w poziomie

Więcej informacji znajdziesz w artykule na temat funkcji SUMA.

Unikanie przepisywania jednej formuły

Po utworzeniu formuły możesz po prostu skopiować ją do innych komórek, zamiast tworzyć tę samą formułę. Możesz skopiować formułę lub użyć uchwytu wypełniania, aby skopiować formułę do sąsiednich komórek.

Na przykład kopiowanie formuły z komórki B6 do komórki C6 powoduje automatyczną zmianę odwołań do komórek w kolumnie C.

Kopiując formułę, sprawdź, czy odwołania do komórek są prawidłowe. Odniesienia do komórek mogą się zmieniać, jeśli są względne. Aby uzyskać więcej informacji, zobacz Kopiowanie i wklejanie formuły do ​​innej komórki lub arkusza.

Czego użyć w formule do symulacji klawiszy kalkulatora?

Klucz kalkulatora	Metoda Excela	Opis, przykład	Wynik
+ (klawisz plus)		Użyj w formule, aby dodać liczby. Przykład: =4+6+2
- (klawisz minus)		Użyj w formule, aby odjąć liczby lub przedstawić liczbę ujemną. Przykład: =18-12 Przykład: =24*-5 (24 razy minus 5)
x (klawisz mnożenia)	* (gwiazdka)	Użyj w formule, aby pomnożyć liczby. Przykład: =8*3
÷ (klawisz podziału)	/ (ukośnik)	Użyj we wzorze, aby podzielić jedną liczbę przez drugą. Przykład: =45/5
% (klucz procentowy)	% (procent)	Użyj * we wzorze, aby pomnożyć przez procent. Przykład: =15%*20
√ (Pierwiastek kwadratowy)	SQRT (funkcja)	Użyj funkcji SQRT w formule, aby znaleźć pierwiastek kwadratowy z liczby. Przykład: =KR(64)
1/x (wzajemne)		Użyj wyrażenia =1/ we wzorze N, Gdzie N- liczba, przez którą należy podzielić 1. Przykład: =1/8

Pliki *.xlsx z przykładami obliczeń wzorów i funkcji. A także bezpłatne próbki: raporty, formularze dokumentów, szablony, prezentacje wykresów i diagramów do samodzielnej nauki.

Pobierz przykłady pracy w Excelu

Pobieranie obliczeń średniej ruchomej w programie Excel.
Pobierz gotowy przykład obliczania i tworzenia prognozy metodą średniej ruchomej. W tym przykładzie wykreślany jest również wykres średniej ruchomej.

Kwota słownie do pobrania.
Funkcja tłumaczy liczbę i kwotę słownie. Rodzaj waluty możesz określić w parametrach funkcji. Gotowy przykład napisanego makra dla funkcji niestandardowej „Liczba słownie i waluta”.

Tabela posortowana według kolumn.
Makro dla przycisków w nagłówkach kolumn tabeli, których kliknięcie umożliwia sortowanie kolumny według innych kolumn. xlsm z przykładem makra działającego na tabeli.

Interaktywna krzyżówka w programie Excel.
Gotowy szablon do wykonywania krzyżówek z wykorzystaniem standardowych funkcji (bez użycia makr VBA) zapewniających interaktywność: sprawdzanie poprawności odpowiedzi, liczenie liczby odpowiedzi.

Program Excel do tworzenia krzyżówek.
Wygodny program stworzony przy użyciu makr VBA, który pomaga szybko tworzyć symetryczne krzyżówki z późniejszym drukiem.

Pobierz przykład obliczenia KTU.
Wzory do obliczania wskaźnika aktywności zawodowej (LCR) do obliczania wynagrodzeń pracowników. Formularz tabeli punktacji.

Pobierz przykład obliczenia CFA.
Wzory do obliczania wskaźnika aktywności finansowej (CFA). Obliczanie salda stabilności finansowej i rentowności przedsiębiorstwa.

Pobierz generator kodów kreskowych w programie Excel.
Makro do generowania 13- i 8-cyfrowych kodów kreskowych bezpośrednio w komórkach arkusza. Kod VBA jest otwarty i edytowalny.

Wykres porównawczy w różnych odmianach.
Przykłady konstrukcji wykresów porównawczych różnego typu: kołowy, słupkowy, histogram, wykres, punktowy, radarowy.

Tabela przekształceń MSSF.
Pobierz tabelę transformacji do budowy modelu budżetowania zgodnie z „International Reporting Standard”.

Tabela transformacji - formularz.
Forma tabeli transformacji do odkurzania danymi przy tworzeniu raportów o dużej dokładności arytmetycznej i przejrzystości korekt.

Obliczanie średnich zarobków w okresie zwolnień.
Przykład obliczenia średnich zarobków przy zwalnianiu pracowników. W wyliczeniu podaje się: średnie zarobki, odprawę oraz odprawę za drugi miesiąc.

Obliczanie średnich zarobków na emeryturę.
Pobierz tabelę w Excelu z przykładem wyliczenia świadczeń emerytalnych według nowych zasad. W kalkulacji uwzględniane są: przeciętne miesięczne zarobki, wskaźnik zarobków, współczynnik stażu pracy, szacunkowa emerytura, wysokość waloryzacji, emerytura ubezpieczeniowa do 2002 r., wskaźnik współczynnika emerytalnego oraz punkty emerytalne.

Przykład obliczenia średnich zarobków za podróż służbową.
Tabela wyliczeń przeciętnych zarobków według listy płac z tytułu wypłaty diet, zarówno pracownikom zatrudnionym na stałe, jak i tym, którzy dopiero rozpoczynają pracę.

Tabela do obliczania średnich zarobków na zwolnieniu lekarskim.
Tabela pokazuje średnie dzienne zarobki do obliczenia zwolnienia chorobowego. Do obliczeń analizowane są wszystkie wyciągi za okres rozliczeniowy.

Kalkulator do obliczania średnich zarobków dla centrum zatrudnienia.
Korzystając z tej tabeli, łatwo jest obliczyć średnie wynagrodzenie dla centrum zatrudnienia według wszystkich standardów i wymagań księgowych. Uwzględniane i obliczane są wszystkie niezbędne wskaźniki: okres rozliczeniowy, kwota i procedura obliczeniowa.

Zautomatyzowana tabela do dokładnego obliczenia stażu pracy z uwzględnieniem lat przestępnych (366 dni) oraz miesięcy z różną liczbą dni kalendarzowych (30, 31, 28 i 29) Z podziałem okresów (dni przepracowanych) na pełne : rok, miesiąc i dzień.

@Leesław , Nie uwierzysz, ale kiedyś też robiłem wnikliwą analizę Lisapedu... Wtedy w mojej głowie zrodził się pomysł, tak jak Ty, zacząłem zgłębiać i studiować JS i sam zrobiłem kalkulator... To wszystko zależy od twoich pragnień..

Twoja formuła oblicza kostki, a wynik działań arytmetycznych (zdradzę wielką tajemnicę) zarówno w Excelu, jak i w JS będzie taki sam!!! (tylko nie ujawniaj tego sekretu publicznie)

Co wstawić liczby do komórek w Excelu, co wstawić te liczby do wejść - wszystko jest uważane za to samo!!!

Jeśli chcesz pięknie zrealizować pomysł, zrealizuj go... No cóż, obliczenie kostek drewna na podstawie obwodu domu, wysokości stropów i grubości belki nie stanowi problemu... To jest dobry pomysł – wprowadź go w życie, a będziesz szczęśliwy: odwiedzający usiądą na Twoim kalkulatorze i „bawią się” nim: Czy zastanawiałeś się, czego chciałeś, czy było to drogie? Zmieniliśmy wiązkę z 200 na 150 (radio lub selekcja) i od razu zobaczyliśmy, o ile taniej było... nadal drogo? zamiast 2,7 przyjęli wysokość sufitu 2,5 - jeszcze taniej... ale nadal drogo? Zamiast 40 mm wzięli deskę podłogową 30 mm - jeszcze taniej... ok, ale ja chcę jeszcze taniej... Zamiast 150 mm zamontowali 100 mm - och, to normalne, wyciągniemy!!! Nacisnęli przycisk i kalkulacja przyszła do nich e-mailem - wydrukowali i od razu przyszli do Ciebie z tą kartką papieru...

Dodatkowe opcje można włączyć/wyłączyć za pomocą pola wyboru (zliczone/niezliczone)...

I wraz z wysłaniem kalkulacji do użytkownika możesz ją wprowadzić do swojej bazy danych - on ją przyniósł i już masz swoją...

Ponadto podczas wysyłania możesz zarejestrować błąd w obliczeniach i e-mail użytkownika - nie dotarł w ciągu tygodnia lub dwóch: napisali automatyczny list - mówią, że popełnili błąd w obliczeniach na stronie, czekamy na Ciebie, witaj...

Tak, możesz myśleć o „gadżetach” jak powóz i mały wózek… Myśl globalnie, ale chwyciłeś kierownicę lizaka…

Wyjaśnij na czym polega problem, czy możesz tworzyć formuły z Excela w js??? W sumie na 2-3 wieczory „rozłóż” ten swój „szalony” tablet na części (używając osobnych wzorów) i utwórz algorytm obliczeniowy. Aby to zakodować - nawet mnie, lamerowi, wystarczy kilka tygodni (jeśli siedzisz od rana do wieczora)... Plusy - kodują w ciągu kilku dni...

Quintessence: Podoba mi się Twój pomysł, jeśli chcesz to zrobić, śmiało, zrób to!!! Jeśli chcesz wstrząsnąć powietrzem i zrobić „bla bla bla”, to nie dla mnie, jest coś pożytecznego do zrobienia… Tylko pamiętaj, że łącząc formularz z witryny z Excelem, jest to całkiem możliwe napisać normalną usługę rozliczeniową. Czy konkurencja, widząc Twoją „krzywą” implementację, przyjmie pomysł i stworzy normalną usługę? A gdzie w tym jesteś? Tak, w tym! I tylko dlatego, że ty Nie Pierwszy!!! „Na koniu” to zawsze ten, kto potrafi patrzeć w przyszłość…

P.S. Jeśli chodzi o złożoność obliczeń: Moje „kalkulatory” z linków są bardzo proste, ale są dla użytkowników, ale w moich planach, po zakończeniu przekształcania witryny w adaptacyjną, zrobię kalkulator do produkcji - nie mniej skomplikowany niż Twój talerz: który obliczy na podstawie tych samych danych, ale nie kosztu zamówienia dla klienta, ale kosztu produkcji... A do tego dochodzą płace akordowe pracowników, prąd i materiały eksploatacyjne, amortyzacja i cała masa innych terminów/zmiennych... I zrobię to - zrobię to!! A kiedy już rozliczę, księgowy odejdzie „do zwolnienia” – zostanie tylko główny księgowy, który sporządza sprawozdania…

P.P.S. A co do tłumaczenia innych kalkulatorów: przy pierwszym będziesz cierpieć - resztę nawet nie zauważysz, jak zrobisz „w locie”...