opetusohjelma näyttää, miten rakentaa kuoletusaikataulu Excel yksityiskohtaisesti määräajoin maksuja lyhennysohjelmainen laina tai asuntolaina.
On lyhennysohjelmainen laina on vain hieno tapa määritellä laina, joka maksetaan takaisin erissä koko laina.
periaatteessa kaikki lainat poistuvat tavalla tai toisella. Esimerkiksi kokonaan 24 kuukauden lyhennysvapaalla on 24 yhtä suurta kuukausimaksua., Jokainen maksu koskee jonkin verran pääomaa ja osa korkoa. Yksityiskohtaisesti jokaisen maksun lainan, voit rakentaa lainan lyhennysaikataulu.
kuoletusaikataulu on taulukko että luettelot säännölliset maksut lainan tai asuntolainasta ajan, hajoaa kukin maksu tulee pääoma ja korko, ja näyttää jäljellä olevan saldon jälkeen kunkin maksun.,
- Miten luoda laina kuoletusaikataulu Excel
- Poistot aikataulu vaihteleva määrä kaudet
- Lainojen lyhennysten aikataulu ylimääräisiä maksuja
- Excel-poistot-malli
Miten luoda laina kuoletusaikataulu Excel
rakentaa lainan tai asuntolainasta kuoletusaikataulu Excel, meidän täytyy käyttää seuraavat toiminnot:
- PMT – toiminto- laskee kokonaismäärä säännöllinen maksu. Summa pysyy vakaana koko lainan keston ajan.,
- PPMT – toiminto- saa pääasiallinen osa kunkin maksun menee kohti lainan pääoma, eli summa, jonka olet lainannut. Määrä kasvaa myöhempien maksujen osalta.
- IPMT – toiminto-löytää jokaisen korkoon menevän maksun koron osan. Summa pienenee jokaisella maksulla.
nyt käydään prosessi vaihe vaiheelta läpi.,
Määritä poistot pöytä
ensinnäkin, määritellä input-solut, jossa voit syöttää tunnetut komponentit lainan:
- C2 – vuotuinen korko
- C3 – laina-aika vuosina
- C4 – maksujen lukumäärä vuodessa
- C5 – lainan määrä
seuraava asia voit tehdä, on luo lyhennystaulukko kanssa etiketit (Kauden, Maksu, Korko, Pääoma, Balance), A7:E7., Ajalta-sarakkeessa, kirjoita numerosarja vastaa kokonaismäärä maksut (1 – 24 tässä esimerkissä):
kaikki tunnetut komponentit paikallaan, mennään mielenkiintoisin osa – lainojen lyhennysten kaavoja.
Laske koko maksun määrä (PMT kaava)
maksun määrä on laskettu PMT(korko, nper, pv, , ) – toiminto.
käsitellä eri maksu taajuudet oikein (kuten viikoittain, kuukausittain, neljännesvuosittain, jne.,), sinun pitäisi olla sopusoinnussa arvojen mukana korko-ja nper perustelut:
- Korko – jaa vuotuista korkoa, jonka määrä maksuaikoja vuodessa ($C$2/$C$4).
- Nper – kerro vuosien määrä maksuaikojen määrällä vuodessa ($C$3*$C$4).
- PV-argumentin osalta merkitään lainamäärä ($C$5).
- fv ja kirjoita perustelut voidaan jättää pois, koska niiden oletusarvot toimivat hienosti meille (tasapaino, kun viimeinen maksu pitäisi olla 0; maksut tehdään lopussa kunkin kauden).,
Asettaa edellä esitetyt väitteet yhteen, saamme kaavan:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
huomioi, että emme käytä absoluuttisia soluviittauksia, koska tämä kaava kopioi alla soluja ilman mitään muutoksia.,
Syötä PMT kaava B8, vedä se alas sarake, ja näet jatkuva maksu määrä kaikki kaudet:
Laske korko (IPMT kaava)
löytää kiinnostavia osa kunkin jakson maksua, käytä IPMT(korko, per, nper, pv, , ) – toiminto:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Kaikki argumentit ovat samat kuin PMT kaava, paitsi per argumentti, joka määrittää maksuaikaa., Tämä argumentti toimitetaan suhteellinen soluviittaus (A8), koska se on tarkoitus muuttaa perustuu suhteellinen asema rivi, johon kaava kopioidaan.,
Tämä kaava menee C8, ja sitten voit kopioida sen alas niin monta solua kuin tarvitaan:
Etsi rehtori (PPMT kaava)
laskea pääasiallinen osa kunkin jakson maksua, käytä tätä PPMT kaava:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
syntaksin ja argumentit ovat täsmälleen samat kuten IPMT kaava edellä:
Tämä kaava menee D-sarakkeeseen, joka alkaa vuonna D8:
Hanki jäljellä
laskea jäljellä kunkin kauden, me voidaan käyttää kahta eri kaavoja.
löytää tasapaino, kun ensimmäinen maksu E8, lisätä enintään lainan määrä (C5) ja rehtori ensimmäisen kauden (D8):
=C5+D8
Koska lainan määrä on positiivinen luku, ja pääoma on negatiivinen numero, jälkimmäinen on itse asiassa vähennetään entinen.,
toisella ja kaikki menestyminen aikoja, lisätä jopa edellisen tasapainoa ja tämän kauden tärkein:
=E8+D9
edellä Oleva kaava menee E9, ja sitten voit kopioida sen alas sarake. Suhteellisten soluviitteiden käytön vuoksi kaava mukautuu oikein kullekin riville.
That ’ s it! Meidän kuukausittain lainojen lyhennysten aikataulu on tehty:
Vihje: Palauta maksuja kuin positiivinen numerot
Koska laina on maksettu pois teidän pankki-tilille, Excel toiminnot palaa maksu, korko ja pääoma yhtä negatiivisia lukuja., Oletusarvon mukaan nämä arvot on merkitty punaisella ja suluissa kuten yllä olevasta kuvasta näkyy.
Jos haluat saada kaikki tulokset positiivisina lukuina, laita miinusmerkki ennen PMT -, IPMT-ja PPMT-funktioita.
Tasapaino kaavoja, käyttää vähennys-sen sijaan, että lisäksi, kuten näkyy kuvakaappaus alla:
kuoletusaikataulun vaihteleva määrä kaudet
yllä olevassa esimerkissä, me rakensimme lainojen lyhennysten aikataulu ennalta määrä maksuaikoja., Tämä nopea kertaratkaisu toimii hyvin tiettyyn lainaan tai asuntolainaan.
Jos etsit luoda uudelleenkäytettäviä poistot aikataulu, jossa on vaihteleva määrä kaudet, sinun täytyy ottaa kokonaisvaltainen lähestymistapa, kuvattu alla.
syötä enimmäismäärä jaksoa,
Kaudella sarakkeen, aseta enimmäismäärä maksut aiot avulla tahansa laina, sanovat, 1 360. Voit hyödyntää Excelin AutoFill-ominaisuutta syöttääksesi sarjan numeroita nopeammin.,
Käyttö, JOS lausunnot poistot kaavat
Koska sinulla on nyt paljon liian pitkä numeroita, sinun täytyy jotenkin rajoittaa laskelmia todellinen määrä maksut tietyn lainan. Tämä voidaan tehdä käärimällä jokainen kaava IF-lausekkeeseen. SIJOITUSRAHASTOSELVITYKSEN looginen testi tarkistaa, onko jaksonumero nykyisellä rivillä pienempi tai yhtä suuri kuin maksujen kokonaismäärä. Jos looginen testi pitää paikkansa, lasketaan vastaava funktio; jos väärä, palautetaan tyhjä merkkijono.,
Olettaen, Erä 1 on rivillä 8, kirjoita seuraavat kaavat vastaavat solut, ja sitten kopioida ne koko taulukko.,
Maksu (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
Etu (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Rehtori (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Tasapaino:
Kauden 1 (E8), kaava on sama kuin edellisessä esimerkissä:
=C5+D8
Jakson 2 (E9) ja kaikki myöhemmät kaudet, kaava ottaa tämän muodon:
=IF(A9<=$C$3*$C$4, E8+D9, "")
seurauksena, sinulla on oikein laskettu poistot aikataulu ja tyhjiä rivejä ajan numeroita, kun laina on maksettu pois.,
Piilota extra kaudet numerot
Jos et voi elää joukko tarpeeton aikana numerot, jotka näkyvät sen jälkeen, kun viimeinen maksu, voit harkita työstä ja ohita tämä vaihe. Jos pyrit täydellisyyteen, Piilota kaikki käyttämättömät kaudet tekemällä ehdollisen muotoilusäännön, joka asettaa fontin värin valkoiseksi mille tahansa riville viimeisen maksun jälkeen.,
tämän, valitse kaikki tietorivit, jos lyhennystaulukko (A8:E367 tässä tapauksessa) ja napsauta aloitus-välilehden > Ehdollinen muotoilu > Uusi Sääntö… > kaavan avulla, mitkä solut muodossa.
Vuonna vastaavaan ruutuun, kirjoita alla kaava, joka tarkistaa, onko erä numero sarakkeessa A on suurempi kuin kokonaismäärä maksut:
=$A8>$C$3*$C$4
sen Jälkeen, valitse Muotoile… – painiketta ja valitse valkoinen fontin väri. Valmis!,
Tee lainan yhteenveto
Voit tarkastella yhteenveto tietoa lainan yhdellä silmäyksellä, lisätä pari enemmän kaavoja yläreunassa kuoletusaikataulun.
maksut Yhteensä (F2):
=-SUM(B8:B367)
korot Yhteensä (F3):
=-SUM(C8:C367)
Jos sinulla on maksut kuin positiivisia lukuja, poistaa miinus-merkki edellä kaavoja.
That ’ s it! Meidän lainan lyhennysaikataulu on valmis ja hyvä mennä!,
Lataa laina kuoletusaikataulu Excel
Miten tehdä laina kuoletusaikataulu ylimääräisiä maksuja Excel
poistot aikataulut edellisessä esimerkkejä on helppo luoda ja seurata (toivottavasti :). He jättävät kuitenkin pois hyödyllisen ominaisuuden, josta monet lainanmaksajat ovat kiinnostuneita – lisämaksut lainan maksamiseksi nopeammin. Tässä esimerkissä tarkastellaan, miten luodaan lainojen lyhennysaikataulu lisämaksuilla.
Määrittele syöttösolut
kuten tavallista, aloita syöttösolujen perustamisella., Tässä tapauksessa anna nimi nämä solut, kuten kirjoitettu alla, jotta meidän kaavoja helpompi lukea:
- InterestRate – C2 (vuotuinen korko)
- LoanTerm – C3 (laina-aika vuosina)
- PaymentsPerYear – C4 (maksujen lukumäärä vuodessa)
- LoanAmount – C5 (yhteensä lainan määrä)
- ExtraPayment – C6 (ylimääräinen maksu per jakso)
Laske ajoitettu maksu
Lukuun ottamatta input-solut, yksi enemmän ennalta solu tarvitaan edelleen laskelmat – aikataulun maksun määrä, eli, lainasta maksettava summa, jos ylimääräisiä maksuja ei suoriteta. Tämä määrä on laskettu seuraavan kaavan mukaisesti:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
huomioi, että laitamme miinus-merkki ennen PMT-funktion tulos on positiivinen numero. Estääksemme virheet, jos osa syöttösoluista on tyhjiä, suljemme PMT-kaavan IFERROR-funktioon.
syötä tämä kaava johonkin soluun (G2 meidän tapauksessamme) ja nimeä se solun ajoitettu maksu.,
Määritä poistot pöytä
Luo lainan lyhennystaulukko kanssa otsikot näkyy kuvakaappaus alla. Kirjoita Jaksosarakkeeseen numerosarja, joka alkaa nollasta (voit tarvittaessa piilottaa jakson 0 rivin myöhemmin).
Jos haluat luoda uudelleenkäytettävän kuoletusaikataulun, syötä mahdollisimman monta maksuaikaa (0-360 tässä esimerkissä).
kauden 0 osalta (rivi 9) vedä saldo-arvo, joka on yhtä suuri kuin alkuperäinen lainamäärä., Kaikki muut solut tämän rivin pysyy tyhjänä:
Kaava G9:
=LoanAmount
4. Rakenna kaavoja poistojen aikataulu lisämaksuilla
Tämä on keskeinen osa työtämme. Koska Excelin sisäänrakennetut toiminnot eivät tarjoa lisämaksuja, joudumme tekemään kaiken matikan itse.
kirjoita seuraavat kaavat riville 10 (Jakso 1) ja kopioi ne sitten alas kaikkien jäljellä olevien kausien osalta.
Ajoitettu Maksu (B10):
Jos ScheduledPayment määrä (nimetty solu G2) on vähemmän kuin tai yhtä suuri kuin jäljellä oleva saldo (G9), käytä maksussa. Muussa tapauksessa lisätään jäljellä oleva saldo ja edellisen kuukauden korko.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
ylimääräisenä varotoimena, me kääri tämä ja kaikki myöhemmät kaavat IFERROR-funktion., Tämä estää joukon erilaisia virheitä, jos osa syöttösoluista on tyhjiä tai sisältävät virheellisiä arvoja.
Ylimääräinen Maksu (C10):
Käytä JOS kaava seuraava logiikka:
Jos ExtraPayment määrä (nimetty solu C6) on pienempi kuin ero jäljellä ja tämä aika on pääasiallinen (G9-E10), paluu ExtraPayment, muuten käyttää ero.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
maksut Yhteensä (D10)
Yksinkertaisesti, lisää määrä maksu (B10) ja ylimääräinen maksu (C10) nykyinen aika:
=IFERROR(B10+C10, "")
Rehtori (E10)
Jos aikataulu maksu tiettynä ajanjaksona on suurempi kuin nolla, tuottoa pienempi näistä kahdesta arvosta: maksussa miinus korot (B10-F10) tai jäljellä (G9); muutoin palaa nolla.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
huomaathan, että pääoma sisältää vain osan maksussa (ei ylimääräistä maksua!) joka menee kohti lainapääomaa.,
Etu (F10),
Jos aikataulu maksu tiettynä ajanjaksona on suurempi kuin nolla, jakaa vuotuinen korko (nimetty solu C2), joita maksujen lukumäärä vuodessa (nimetty solu C4) ja kerrotaan tulos tasapaino, jälkeen jäljellä edellisen jakson aikana; muutoin palauttaa 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
Tasapaino (G10)
Jos jäljellä (G9) on suurempi kuin nolla, vähennä pääasiallinen osa maksu (E10) ja ylimääräinen maksu (C10) alkaen tasapaino joka jää jäljelle, kun edellisellä kaudella (G9); muutoin palauttaa 0.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
Jos kaikki on tehty oikein, sinun laina kuoletusaikataulu tässä vaiheessa pitäisi näyttää tältä:
Piilota extra kaudet
Määritä ehdollisen muotoilun sääntö piilottaa arvoja käyttämättömät kaudet, kuten tässä vinkki.,maksut vuodessa:
=LoanTerm*PaymentsPerYear
Todellinen määrä maksut:
Count solujen Yhteensä Maksu-sarakkeessa, joka on suurempi kuin nolla, alkaa Jaksolla 1:
=COUNTIF(D10:D369,">"&0)
Yhteensä ylimääräisiä maksuja:
Lisää enintään solujen Ylimääräistä Maksu-sarakkeessa, alkaa Jaksolla 1:
=SUM(C10:C369)
korot Yhteensä:
Lisää solut Edun sarakkeessa, alkaa Jaksolla 1:
=SUM(F10:F369)
Vaihtoehtoisesti, piilota Aikana 0 rivi, ja laina kuoletusaikataulu ylimääräisiä maksuja on valmis!, Kuvakaappaus alla näyttää lopullisen tuloksen:
Lataa laina kuoletusaikataulu ylimääräisiä maksuja,
kuoletusaikataulu Excel-malli
tehdä huippuluokkaa lainojen lyhennysten aikataulu ei ole aikaa, käyttää Excelin sisäänrakennettu malleja. Mene Tiedosto > Uusi tyyppi ”kuoletusaikataulun” hakukenttään ja valitse malli pidät, esimerkiksi tämä yksi kanssa ylimääräisiä maksuja:
tämä on, miten voit luoda lainan tai asuntolainasta kuoletusaikataulu Excel., Kiitän teitä lukemisesta ja toivon näkeväni teidät blogissamme ensi viikolla!
Saatavilla lataukset
kuoletusaikataulun Näyte-Työkirja
- Miten laskea korkoa korolle, Excel
- Miten löytää CAGR (compound annual growth rate) Excel
- Lasketaan prosentteina Excel kaava esimerkkejä