Kalkulator (excel) zdolności kredytowej - prośba o komentarze

[yeszie]

Witam,

przygotowałem kalkulator obliczający ile trzeba zarobić (wykazać dochodu i odprowadzić podatku) aby przygotować pod kredyt hipoteczny.

 

Być może komuś się przyda. Chciałbym kalkulator udoskonalić - dlatego prośba do osób które wiedzą jak banki liczą zdolność o komentarze.

 

Kalkulator zakłada uzyskiwanie przychodów z tytułu prowadzonej DG oraz wynajmu (rozliczanych osobno przez każdego z małżonków).

 

Nie wiem jakie są też kwoty jakie banki liczą jako - koszt życia (wpisałem 500 zł). Stąd też prośba o informacje gdyż ma to duże znaczenie. Zawyżanie tej kwoty wpływa na konieczność opłacenia większych PD.

 

Arkusz zakłada przygotowanie zdolności kredytowej dla określonej kwoty (tu 100 tyś).

 

Zdolność od dochodu z tyt. wynajmu mieszkań obniżona do 70% choć część banków bierze 80%.

 

Niestety nie mogę umieścić linku do kalkulatora gdyż serwis nie umożliwia mi tego (zabezpieczenie przed spamem).

 

W załączeniu więc zamieszczam screen arkusza.

[PlanFinance]

Twój pomysł jest ciekawy. Jednakże muszę Cię zmartwić. Twoja praca nie ma żadnego sensu. Jeszcze nikt nie zrobił uniwersalnego, poprawnie działającego kalkulatora.

[slawek001]

witam

 

Twój pomysł jest ciekawy. Jednakże muszę Cię zmartwić. Twoja praca nie ma żadnego sensu. Jeszcze nikt nie zrobił uniwersalnego, poprawnie działającego kalkulatora

 

Jak widzę odgrzewam bardzo stary temat ..... zostałem "zmuszony" do wykonania kalkulatora kredytowego .... Choć znalazłem w internecie Kalkulator Kredytowy wykonany w 1997 r przez p. Marek Suski, który wyznacza raty dla metody Annuitetowej i Kapitałowej nadal nie spełnia wszystkich warunków jakie chcę otrzymać.

Wykonując swój kalkulator zatrzymałem się w punkcje który tez przez długi czas nie mógł pokonać p. Łukasz Maj, a mianowicie wyliczenie automatycznie oprocentowania.

Zatrzymałem się w tym samym punkcie a mianowicie, jak posiadając dane: Kwota kredytu z kosztem odsetkowym, kwota kredytu, liczba rat, koszt kredytu, kwota raty, można obliczyć rzeczywiste oprocentowanie kredytu .... każda podpowiedz jest miło widziana

[slawek001]

witam

 

Twój pomysł jest ciekawy. Jednakże muszę Cię zmartwić. Twoja praca nie ma żadnego sensu. Jeszcze nikt nie zrobił uniwersalnego, poprawnie działającego kalkulatora

 

Jak widzę odgrzewam bardzo stary temat ..... zostałem "zmuszony" do wykonania kalkulatora kredytowego .... Choć znalazłem w internecie Kalkulator Kredytowy wykonany w 1997 r przez p. Marek Suski, który wyznacza raty dla metody Annuitetowej i Kapitałowej nadal nie spełnia wszystkich warunków jakie chcę otrzymać.

[ATTACH=CONFIG]5712[/ATTACH]

Wykonując swój kalkulator zatrzymałem się w punkcje który tez przez długi czas nie mógł pokonać p. Łukasz Maj, a mianowicie wyliczenie automatycznie oprocentowania.

[ATTACH=CONFIG]5713[/ATTACH]

Zatrzymałem się w tym samym punkcie a mianowicie, jak posiadając dane: Kwota kredytu z kosztem odsetkowym, kwota kredytu, liczba rat, koszt kredytu, kwota raty, można obliczyć rzeczywiste oprocentowanie kredytu .... każda podpowiedz jest miło widziana

[ATTACH=CONFIG]5714[/ATTACH]

[Pistolero]

kluczem do rozwiązania Twojej zagadki są excelowskie funkcje IRR i NPV oraz PMT. - zachęcam do zapoznania się ze sposobem działania tych funkcji i konstrukcją wzorów.

Ponadto przydatna będzie opcja "goalseek" - szukanie wyniku np poprzez zarejestrowanie makra.

 

Warto stworzyć sobie tabelę roboczą.

Mając ww dane stworzenie harmonogramu dla oprocentowania nominalnego nie powinno być problemowe.

 

Następnie w kolumnach należy zdyskontować raty do wartości bieżącej (będą malały) [kwota raty/(1+RRSO)^(numer raty w szeregu/12)] - i tak dla każdej z osobna

 

RRSO to nic innego niż tak dobrana stopa procentowa dla której suma zdyskontowanych rat + koszty około-kredytowe (prowizja, ubezpieczenia itd) dadzą łącznie wartość bieżącą otrzymanego kapitału netto. (tutaj właśnie przydaje się "goalseek")

 

Niestety zrobienie tego w excelu jako automatyczny proces (aby robiło się automatycznie w tle jako funkcja) jest IMO niemożliwe.

Zawsze albo ręcznie należy wyołać szukanie wyniku albo zarejestrować makro i podpiąć pod "przycisk" lub inny wyzwalacz procesu.

 

Pozdrawiam

[slawek001]

Niestety zrobienie tego w excelu jako automatyczny proces (aby robiło się automatycznie w tle jako funkcja) jest IMO niemożliwe

 

Od razu powiem .... pisać arkusze excel nauczyłem się na forum gier liczbowych, pisałem tam arkusze pod nazwą kodową Hybryda posiadają duże prawdopodobieństwo wyjścia liczb w zbiorze liczbowym sięgające 60%. Tu nie ma prawdopodobieństwa jest "stała", wręcz muszę napisać ten arkusz, żeby mógł pomóc każdej osobie która po latach spłaconego kredytu otrzyma wiadomość od FW że musi coś zapłacić i jedynym dokumentem jaki będzie posiadać potwierdzającą racje będzie raport BIK.

 

Zawsze albo ręcznie należy wyołać szukanie wyniku albo zarejestrować makro i podpiąć pod "przycisk" lub inny wyzwalacz procesu.

 

Z tym twierdzeniem się nie zgodzę .... w grach liczbowych opracowałem "motor" który porównując wyniki podawał tylko ten wynik jaki miał zapisany za wzór. Tutaj stałymi są: kwota kredytu, okres kredytowania, odsetki spłaty kredytu, ogólny koszt kredytu .... nieznane jest oprocentowanie i oprocentowania "motor" będzie szukał.

 

 

Jest to zdjęcie kalkulatora wykonanego przez Łukasz Maj, ręcznie właściwe oprocentowanie ustawiłem, automatycznego ustawienia oprocentowania nie mógł wykonać, jak to czytałem na forum gdzie pomocy wykonania szukał. Ja odwrotnie szukam pomocy jak wykonać co przedstawia zdjęcie żeby pod arkusz podpiąć "automat" .... Jestem w tym "zielony" choć w analizach gier liczbowych daje sobie rade.

[Pistolero]

podałem receptę, jak to wykorzystasz Twoja sprawa. Masz niemalże wszystkie dane ze wzoru na ratę kredytu - jak odpowiednio przestawisz wzór dostaniesz oprocentowanie.

Tyle, że oprocentowanie praktycznie nigdy nie jest równe RRSO.

 

Zachęcam też do lektury rekomendacji S. Tam też są wytyczne jak banki powinny liczyć RRSO (co uwzględniać) aby wyniki różnych symulacji były porównywalne.

 

wiem na swoim przykładzie że jednym kliknięciem bardzo ładnie liczy się RRSO.

 

"Rozbierałem" kilka kalkulatorów kredytowych oraz IWC z różnych banków, gdzie banki robiły te narzędzia na excelu i NIGDZIE nie spotkałem się z rozwiązaniem, aby RRSO liczyło się dynamicznie zawsze było to podpisane pod makro/VBA lub pod manualnie napisaną funkcję.

 

O ile przy krótkich okresach kredytowania zrobienie "automatu" jest jeszcze możliwie np używając funkcji domyślnych w excelu o tyle jak pojawi się np 300 rekordów/rat to pętla się masakrycznie rozbudowuje.

Nie pamiętam już ile razy przy NPV excel wykonuje pętlę do znalezienia wyniku, ale nie była to duża wartość - a jak nie znajduje to wywala błąd w formule = nieskuteczne.

[slawek001]

Moduł - Czas trwania kredytu

 

 

szare pola - gdzie wprowadzamy dane, żółte podające wynik

 

czas trwania kredytu możemy ustalić podając daty początku i końca kredytu lub datę początku kredytu i ilość rat.

Wynik to: ilość dni lub ilość miesięcy lub ilość lat

Dla kredytu określonego według dat: powstania kredytu 1999-12-20, zakończenia kredytu 2001-12-20, mamy wyniki: Dni 731, miesięcy 24, lat 2

Dla kredytu określonego według miesięcy: powstanie kredytu 1999-12-20, liczba rat 13 (data zakończenia zobowiązania 2001-01-20), mamy wyniki: Dni 397, miesięcy 13, lat 1 (dopiero po pełnych okresach 24,36,48podawane są faktyczne lata)

[Pistolero]

i....???

 

co to ma do RRSO i automatycznego wyliczania oprocentowania?

[slawek001]

Moduł - Oprocentowanie

 

 

szare pola - gdzie wprowadzamy dane, żółte podające wynik

 

Tutaj zmienne to: Data powstania kredytu, kwota kredytu z kosztem odsetkowym, kwota kredytu, ilość rat.

stałe to: czas trwania kredytu podany w dniach, czas trwania kredytu po przeliczeniu 30 dni miesiąc, koszt kredytu, kwota raty

 

Twojej zagadki są excelowskie funkcje IRR i NPV oraz PMT. - zachęcam do zapoznania się ze sposobem działania tych funkcji i konstrukcją wzorów

 

PMT sprawdzałem na tym etapie nie pasuje do mojej analizy, IRR i NPV nie sprawdzałem może wykona co chcę osiągnąć

[slawek001]

Dlatego nazwa kodowa arkuszy jest Hybryda .... wydaje się że to nic wspólnego nie ma ... bo wszystko rozebrane jest na czynniki pierwsze

[Pistolero]

bardzo proszę

 

[slawek001]

Zgadza się tak wygląda tabela spłat ... dokładne rzeczywiste oprocentowanie wynosi 42,4165%, po zaokrągleniu daje 42,42%.

 

Wracając do wykonywanego arkusza, jak sądzę z problemem poradzę sobie pisząc arkusz w "formułach" ... pierwsze próby wyszły dość pomyślnie. Arkusz ma za zadanie określić rzeczywiste oprocentowanie kredytu i pokazać jaką sumę musiał bym spłacić w zależności jak krótszy był by okres spłacania kredytu.

 

 

Wygląd arkusza i tak będzie jutro zmieniony, nie uwzględniłem danych z umowy kredytu, a właśnie te dane "automatycznie" określają oprocentowanie rzeczywiste

[slawek001]

Jest to część sprawdzająca czy: rzeczywiste oprocentowanie zostało prawidłowo przeliczone

 

 

Opis:

Rodzaj rat: stałe

 

Współczynnik "Q": =JEŻELI(E3=H1;(E12/12)+1;"---")

Gdzie:

Przycisk zmieniający rodzaj rat (stałe, malejące) – dla rat stałych: E3=H1

Przycisk zmieniający rodzaj rat (stałe, malejące) – dla rat malejących: "---"

Rzeczywiste oprocentowanie w skali roku dzielone przez ilość miesięcy: E12/12

 

Okres kredytowania (miesiące): =P5

Gdzie:

Ilość miesięcy: =((ROK(P3)-ROK(P2))*12+MIESIĄC(P3)-MIESIĄC(P2))

Data powstania kredytu P2

Data zamknięcia kredytu P3

 

Stała miesięczna rata: =JEŻELI(E3=H1;E6*E14^E13*(E14-1)/(E14^E13-1);"---")

Gdzie:

Przycisk zmieniający rodzaj rat: E3=H1

Kwota kredytu (kredyt otrzymany do reki): E6

Współczynnik "Q": E14

Okres kredytowania (miesiące): E13

 

Ogółem do banku zwrócimy: =JEŻELI(E3=H1;E18*E13;E6+E22)

Gdzie:

Przycisk zmieniający rodzaj rat: E3=H1

Stała miesięczna rata: E18

Okres kredytowania (miesiące): E13

Kwota kredytu (kredyt otrzymany do reki): E6

Odsetki doliczone przez okres spłaty kredytu: E22

 

Odsetki doliczone przez okres spłaty kredytu: =JEŻELI(E3=H1;E23-E6;(E21*E13)*6)

Gdzie:

Przycisk zmieniający rodzaj rat: E3=H1

Odsetki doliczone przez okres spłaty kredytu: E23

Kwota kredytu (kredyt otrzymany do reki): E6

Średnie miesięczne odsetki: E21

Okres kredytowania (miesiące): E13

 

Średnie miesięczne odsetki: =JEŻELI(E3=H1;E22/E13;(E16+E17)/12)

Gdzie:

Przycisk zmieniający rodzaj rat: E3=H1

Odsetki doliczone przez okres spłaty kredytu: E22

Okres kredytowania (miesiące): E13

Rata odsetkowa (1-sza rata): E16 (dla rat malejących)

Rata odsetkowa (ostatnia rata): E17 (dla rat malejących)

 

Dalej zajmę się automatycznym ustaleniem rzeczywistego oprocentowania RRSO, bym był wdzięczny za pomoc w wamianie wzoru na formuły Excel.

[slawek001]

I padam na matematyce .... szukam pomocy w przekształceniu wzoru

 

Współczynnik "Q" = (rzeczywiste oprocentowanie / miesięcy w roku) + 1

 

Rz – rzeczywiste oprocentowanie

R – miesięcy w roku

W – Współczynnik "Q"

 

W = (Rz / R) + 1

 

Przekształcenie wzoru żeby obliczyć rzeczywiste oprocentowanie

 

Rz = (W * R) +1

 

Rzeczywiste oprocentowanie = (Współczynnik "Q" * miesięcy w roku) + 1

 

Stała rata miesięczna = kwota kredytu * Współczynnik "Q" ^ Okres kredytowania * Współczynnik "Q"/Okres kredytowania

 

S – stała rata miesięczna

K – kwota kredytu

W – Współczynnik "Q"

O – okres kredytowania

 

S = K*W^O*W/O

 

Jak przekształcić wzór żeby obliczyć W

W =?

Współczynnik "Q” =?

[slawek001]

Błędnie podałem wzór który chcę przekształcić poprawie się:

 

S = K*W^O*(W-1)/(W^O-1)

 

Jak obliczyć W = ?

 

ma to być część wzoru do obliczenia rzeczywistego oprocentowania dla kredytu udzielonego w ratach stałych .... plisssssss

[slawek001]

Wykonanie automatycznego obliczenia rzeczywistego oprocentowania dla rat stałych jest trudniejsze niż sądziłem .... mam prośbę ... proszę o wykonanie testu arkusza pod względem poprawnego podawania kwoty przy dokonaniu wcześniejszej spłaty kredytu

 

arkusz do pobrania ...... speedy.sh/kde9r/Kalkulator-kredytowy-Hybryda-0.1-test.xls

[slawek001]

Z wielkim szacunkiem dla autora tekstu:

 

Niestety zrobienie tego w excelu jako automatyczny proces (aby robiło się automatycznie w tle jako funkcja) jest IMO niemożliwe

.

 

Fałszywe stwierdzenie .... możliwe jest wykonanie w Excelu jako automatyczny proces podanie rzeczywistego oprocentowania kredytu jako proces automatyczny działający w tle, dla rat stałych i rat malejących oddzielnie.

Wykonanie automatycznego określania rzeczywistego oprocentowania w jednym arkuszu jako przełącznik rodzaju rat zawiesza arkusz.