Tiesitkö, että Excel voi laskea lainan takaisinmaksut? Tässä artikkelissa käydään läpi kaikki tarvittavat toimenpiteet. (Katso myös: Asuntolaskimet: miten he työskentelevät .)

Excelin avulla saat paremman käsityksen kiinnityksestäsi kolmessa yksinkertaisessa vaiheessa. Ensimmäinen vaihe on kuukausimaksun määrittäminen. Toinen on löytää korko, ja kolmas on löytää laina-aikataulu. Voit tehdä tämän Excel-taulukon, joka kertoo sinulle: Suurin hinnat; lainan laskenta kestoon; lainan laiminlyönti sekä kuukausittaisen vuokran kuoletukset ja laskelmat.

Lainan laskeminen kuukausittaiselle vuokrasopimukselle

Ensinnäkin katsotaan, miten lasketaan kuukausittainen maksu asuntolainalle. Toisin sanoen vuosittaisen koron, pääoman ja keston avulla voimme määrittää kuukausittain maksettavan määrän.

Kaava, kuten kuvassa näkyy, on kirjoitettu seuraavasti:

= - PMT (nopeus, pituus, nykyarvoarvo, tulevaisuuden arvo) [tyyppi])

PMT: n edessä oleva miinusmerkki on tarpeen, koska kaava palauttaa negatiivisen numeron. Kolme ensimmäistä argumenttia ovat lainan korko, lainan pituus (kausien lukumäärä) ja lainanottaja. Viimeiset kaksi argumenttia ovat valinnaisia, jäännösarvo on oletuksena 0, maksetaan etukäteen (1) tai lopussa (0), on myös vapaaehtoinen.

= - PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) - 3 ->

= PMT ((1 + 3, 10%) ^ (1/12) -1, 10 * 12, 120000)

Selitys: Käytämme nopeutta, joka on kuukausittainen korko, kausien lukumäärä (kuukautta täällä 120 kertaa kymmenen vuotta kerrottuna 12 kuukaudella) ja lopuksi mainitsemme pääoman lainaksi. Kuukausimaksumme ovat $ 1, 161. 88 yli 10 vuotta.

Kiinnityslaskenta korkojen mukaan

Olemme havainneet, miten lasketaan kuukausittainen maksu asuntolainalle. Mutta voimme haluta asettaa enimmäiskorvauksen, jolla meillä on varaa, joka näyttää myös vuosien määrän, jona meidän olisi maksettava se takaisin. Tästä syystä haluamme tietää vastaavan vuosikoron.

Lainan koron laskeminen

Kuten yllä olevasta kuvaruudusta näkyy, lasketaan ensin kausi (kuukausittain meidän tapauksessamme) ja sitten vuotuinen korko. Käytetty kaava on RATE, kuten yllä olevassa kuvaruudussa näytetään, seuraavasti:

= RATE (Nper; pmt; present_value; [future_value]; [tyyppi])

Kolme ensimmäistä argumenttia ovat laina (kausien määrä) ja kuukausittainen maksu lainan ottamiseksi takaisin. Viimeiset kolme argumenttia ovat valinnaisia, ja jäännösarvo on oletusarvo 0, termi argumentti maturiteetin hallitsemiseksi etukäteen (1) tai lopussa (0) on myös vapaaehtoinen, ja lopuksi arviointitapa on valinnainen, mutta voi anna alustava arvio nopeudesta.

Laskentakoron laskemiseen käytetty Excel-kaava on

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Huomautus: kuukausimaksulla on annettava negatiivinen merkki. Siksi miinusmerkki ennen kaavaa. Meidän korko on 0. 294%.

Käytämme kaavaa = (1 + B5) on 12-1 ^ = (1 +0,24%) ^ 12-1 saadaksemme lainan vuotuisen koron olevan 3. 58 %. Toisin sanoen, lainata 120 000 dollaria yli 13 vuoden ajan kuukausittaisen 960 $: n takia, meidän on neuvoteltava laina vuosittain 3. 58%: n enimmäisnopeudella.

Asuntolainan laskenta lainan pituudelle

Näemme nyt, miten lainan pituus saadaan, kun tiedät vuotuisen koron, pääoman lainan ja kuukausittaisen maksun, joka on maksettava takaisin. Toisin sanoen, kuinka kauan meidän on maksettava takaisin 120 000 dollarin kiinnitys, jonka korko on 3. 10% ja kuukausimaksu 1 100 dollaria?

Lainojen takaisinmaksujen määrä

Käytettävä kaava on NPER, kuten yllä olevassa kuvakaappauksessa on esitetty, ja se on kirjoitettu seuraavasti:

= NPER (korko, pmt; present_value; [future_value]; [tyyppi])

Kolme ensimmäistä väitettä ovat lainan vuotuinen korko, lainan takaisinmaksuun tarvittava kuukausimaksu ja lainanottaja. Viimeiset kaksi argumenttia ovat valinnaisia, jäännösarvo on oletusarvo 0, etukäteen (1) tai lopussa (0) maksettava käsite on myös vapaaehtoinen.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1, 1100, 120000)

Huomaa: kuukausimaksun vastaaviin tietoihin on merkittävä negatiivinen merkki. Siksi meillä on miinusmerkki ennen kaavaa. Korvauksen pituus on 127. 97 jaksoa (kuukausi meidän tapauksessamme).

Käytämme kaavaa = B5 / 12 = 127. 97/12 vuosia, jolloin lainan takaisinmaksu toteutetaan. Toisin sanoen lainaamaan 120 000 dollaria, jonka vuotuinen korko on 3. 10% ja maksaa 1 100 dollaria kuukaudessa, meidän olisi maksettava erääntymisaika 128 kuukautta tai 10 vuotta ja 8 kuukautta.

Lainan purkaminen

Lainan maksu koostuu kahdesta asiasta, pääomasta ja korosta. Korko lasketaan kultakin ajanjaksolta, esimerkiksi 10 vuoden yli tapahtuvan kuukausittaisen takaisinmaksun ansiosta.

Yllä oleva kuvakaappaus osoittaa lainan erittelyn (yhteensä 120 kertaa) käyttämällä PPMT- ja IPMT-kaavoja. Kahden kaavan argumentit ovat samat, ja ne jakautuvat seuraavasti:

= - PPMT (nopeus, num_period, pituus, päämies, [jäännös], [terme])

= - INTPER (nopeus, num_period; [terme])

Argumentit ovat samat kuin ensimmäisessä osassa esitetyn PMT-kaavan osalta, lukuun ottamatta num_periodia, joka lisätään osoittamaan ajanjakso, jona laina erotellaan, jolloin pääomasta ja kiinnostuksesta. Esimerkiksi:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10 %) ^ (1/12) -1, 1, 10 * 12, 120000)

Tulos on se, joka näkyy kuvakaappauksessa "Loan Decomposition" analysoitavana ajanjaksona, joka on "1". , tai ensimmäisen kuukauden.Tästä maksamme 1161 dollaria. 88, eriteltyinä 856 dollariin, 20 pääomaan ja 305 dollariin. 68 korkoa.

Excel-laina-laskenta

Nyt voidaan myös laskea pääoman ja korkojen takaisinmaksua useille jaksoille, kuten ensimmäisille 12 kuukaudelle tai ensimmäisille 15 kuukaudelle.

= - CUMPRINC (nopeus, pituus, principal, start_date, end_date; type)

= - CUMIPMT (nopeus, pituus, principal, start_date, end_date; päämies ja termi (jotka ovat pakollisia), joita näimme jo ensimmäisessä osassa kaavan PMT kanssa. Mutta täällä, tarvitsemme myös start_date- ja end_date-argumentit. Ensimmäinen kertoo analysoitavan ajan alku ja toinen loppu. Esimerkiksi:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

(1 + B2) ^ (1/12) -1, B4 * 12, B3 (1/12) -1, 10 * 12, 120000, 1, 12, 0) 1, 12, 0)

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1, 10 * 12, 120000, 1, 12, 0)

joka esitetään kuvakaappauksessa "Cumul 1. vuosi", joten analysoidut kaudet vaihtelevat välillä 1-12, ensimmäisen jakson (ensimmäinen kuukausi) kahdestoista (12. kuukausi). Yli vuoden päästä maksettaisiin $ 10 419, 55 Principal ja $ 3 522. 99 Kiinnostusta.

Lainan lyhennys

Aikaisemmilla kaavoilla voimme luoda aikataulujamme ajanjaksolla, kuinka paljon maksamme kuukausittain pääomasta ja korosta ja kuinka paljon on jäljellä.

Luottoaikataulun luominen Excelissä

Luottoaikataulun luomiseksi käytämme edellä mainittuja kaavoja ja laajennamme niitä kausien lukumäärän mukaan.

Syötä ensimmäisen jakson sarakkeessa ensimmäistä jaksoa "1" ja vedä solu alas. Meidän tapauksessamme tarvitsemme 120 jaksoa, kun 10 vuoden lainamaksu kerrottuna 12 kuukaudella = 120.

Toinen sarake on kuukausittainen kuukausittainen summa, joka on vakio koko laina-aikataulun mukaan. Laske se, lisää seuraavan kaavan soluun:

= - PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12 ) -1, 10 * 12, 120000)

Kolmas sarake on tärkein, joka maksetaan kuukausittain. Esimerkiksi 40. ajanjaksolle maksamme 945 euroa. 51 pääomaa kuukausittain yhteensä $ 1, 161. 88. Lunastetun pääoman laskemiseksi käytämme seuraavaa kaavaa:

= - PPMT (TP, A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Neljäs sarake on kiinnostus, jonka laskemalla kuukausittainen summa palautetaan paljon korkoa on maksettava käyttäen kaavaa:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12) ; 1; 10 * 12; 120000)

Viides sarake sisältää maksettavan määrän. Esimerkiksi 40. maksun jälkeen meidän on maksettava 83, 994. 69 dollaria 120 000 dollaria. Kaava on seuraava:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1, A18, 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12), 10 * 12, 120000, 1, 1, 0)

päämiehen kanssa eräässä ajanjaksossa solun kanssa, jonka pääoma on lainattu. Tämä aika alkaa muuttua, kun kopioimme ja vedämme solun alas.Alla oleva kuvakaappaus osoittaa, että 120 kauden lopussa lainaamme maksetaan takaisin.