10 trinn for å lage en sparekalkulator med flere scenarier i Excel

Å spare penger er en viktig del av enhver sunn økonomisk plan. Uansett hva du sparer for, jobber du med de samme par økonomiske dataene: en månedlig investering eller et mål, en rente og antall måneder eller år du har tenkt å spare. Et Excel-ark kan hjelpe deg med å gjøre alle data om til informasjon du kan bruke til å finjustere spareplanen din. Disiplin kan være nøkkelen, men kunnskap hjelper deg å ta de beste beslutningene. Det er ganske enkelt å bygge en kalkulator med flere scenarier i Excel, og vi skal vise deg hvordan det gjøres. (Hvis du foretrekker det, kan du ganske enkelt laste ned vår forhåndsbygde versjon av denne kalkulatoren.)

1: Skriv inn etikettene

Skriv inn noen etiketter for å komme i gang. Bruk figur A (den komplette kalkulatoren) som en guide, og skriv inn følgende etiketter:

B1: Sparekalkulator

B3: Framtidig verdi av månedlig investering

B4; B10; B15: Hva er den årlige renten?

B5; B11; B16: Hvor mange år vil du spare?

B6: Hvor mye har du tenkt å spare hver måned?

B7; B17: Hvor mye er innskuddet ditt (et depositum er ikke nødvendig)?

B8: Den fremtidige verdien av den månedlige investeringen er:

B9: Framtidig verdi av engangsinvestering

B12: Hvor mye er engangsutbetalingen din?

B13: Den fremtidige verdien av en engangsinvestering er:

B14: Månedlig investering som kreves for å oppfylle sparemålet

B18: Hva er sparemålet ditt?

B19: Den månedlige betalingen for å nå sparemålet ditt er:

Figur A

Angi informasjonsetiketter og instruksjoner for brukere om å legge inn økonomiske data.

2: Angi formlene

Det er tre formler i denne sparekalkulatoren:

 C8: = IF (ISERROR (FV (C4 / 12, C5 * 12, -C6, -C7)), "Feil", FV (C4 / 12, C5 * 12, -C6, -C7)) 

C13: = IF (ISERROR (FV (C10, C11, 0, -C12)), "Feil", FV (C10, C11, 0, -C12))

C19: = IF (ISERROR (PMT (C15 / 12, C16 * 12, -C17, C18)), 0, PMT (C15 / 12, C16 * 12, -C17, C18))

Excel's FV () -funksjon returnerer fremtidig verdi på en investering. PMT () -funksjonen beregner den månedlige betalingen som kreves for å oppfylle et spesifikt mål. (Du kan også bruke denne funksjonen til å bestemme utbetalinger på et lån.)

Funksjonene = IF () og ISERROR () fungerer sammen for å forhindre Excel-feilverdier når inputcellene er tomme. Hvis formelen returnerer en feil, viser formelen strengen "Feil." Riktignok er ikke strengen "Feil" mye mer nyttig enn en Excel-feilverdi. Poenget er kontroll - når du lager din egen kalkulator, kan du vise feilmeldingene du synes er mest passende for brukerne dine. Hvis du bruker Excel 2007 eller 2010, bruker du IFERROR ().

3: Formater etikettcellene

Formatering skal hjelpe brukere, ikke å distrahere dem fra kalkulatorens formål. Flere celler krever formatering. Heldigvis kan du tilordne formater til valg av flere celler som følger:

  1. Hold nede Ctrl -tasten mens du klikker på utvetydige celler med lignende format. Eller hold nede Skift -tasten mens du velger de første og siste cellene i en sammenhengende blokk.
  2. Høyreklikk på utvalget og velg Formater celler fra den resulterende hurtigmenyen.
  3. Bruk formatet slik du normalt gjør på en enkelt celle.

Ved hjelp av instruksjonene ovenfor og tabell A, bruk riktig formatering.

Tabell A

Etikettformater

4: Formater formelcellene

Det er bare tre formelceller, og du vil bruke de samme formatene på alle tre, så dette trinnet er enkelt:

  1. Velg celler C8, C13 og C19 (nr. 3).
  2. Høyreklikk på utvalget og velg Format celler.
  3. Klikk kategorien Tall.
  4. Velg valuta fra kategorilisten.
  5. Angi 2 desimaler.
  6. Velg rødt uten parenteser for formatet med negativt tall. Dette er ikke avgjørende, men parentesene i resultatene kan forvirre brukere.
  7. Klikk OK.

Når markeringen fremdeles er intakt, bruker du fet skriftattributt.

5: Formater inndatacellene

Hvert sparscenario har sitt eget sett med inndataceller, men du kan formatere grupper av dem samtidig ved å bruke et flercelleutvalg (# 3). Se tabell B for å bruke de riktige formatene.

Tabell B

Formater inndataceller

6: Legg til validering til inputceller

Du kan eliminere frustrasjon fra brukeren ved å bruke valideringsregler på inputcellene. Ellers kan brukere legge inn upassende verdier. Valideringsregelen som brukes i den nedlastbare arbeidsboken, begrenser inndata til hvilket som helst gyldig nummer . Gjør følgende for å bruke denne valideringsregelen:

  1. Velg celle C4.
  2. Velg Validering fra Data-menyen. I Excel 2007 og 2010, klikker du på fanen Data | Datavalidering.
  3. I kategorien Innstillinger velger du Tilpasset fra Tillat-kontrollen.
  4. Skriv inn = ISNUMBER (C4) i formelkontrollen . Ikke gjør cellehenvisningen absolutt! Du får se hvorfor om et øyeblikk.
  5. Klikk på inndatameldingen og skriv inn følgende innstillinger:

Tittel: Inndataverdi

Melding: Tast inn et nummer.

  1. Klikk på fanen Feilvarsel og skriv inn følgende innstillinger:

Tittel: Inndataverdi

Melding: Tast inn et nummer.

Kopier valideringsregelen til de andre inputcellene som følger:

  1. Velg C4.
  2. Velg Kopier fra Rediger-menyen. I Excel 2007 og 2010 klikker du på Hjem-fanen | Kopier (i utklippstavle-gruppen).
  3. Hold nede Ctrl -tasten og klikk på cellene C5, C6, C7, C10, C11, C12, C15, C16, C17 og C18.
  4. Velg Lim inn spesial fra Rediger-menyen. I Excel 2007 og 2010 klikker du på Hjem-fanen | Lim inn Lim inn spesial.
  5. Klikk Validering-alternativet i Lim inn-delen.
  6. Klikk OK.

Den relative celleadressen i ISNUMBER () -funksjonen lar deg kopiere valideringsregelen til andre inndataceller.

7: Legg til litt pynting

Selv om det ikke er nødvendig, kan det være lurt å spiffe opp ting bare litt. Den nedlastbare arbeidsboken inneholder en logo, og bakfargen på de tre tittelcellene stemmer overens med det blå i logoen. I tillegg skisserer grenser kalkulatorområdet (B1: C10). Radhøyden er litt større enn standard. Forsikre deg om at kolonne B er bred nok til å vise spørsmålene.

For å slå av skjermen for noen få funksjoner brukere ikke trenger, gjør du følgende i Excel 2003:

  1. Velg Alternativer fra Verktøy-menyen.
  2. Klikk kategorien Vis.
  3. Fjern merket for rutenettlinjer og rad & kolonneoverskrifter i vinduet Alternativer-delen.
  4. Fjern merket for Formel Bar i Show-delen.
  5. Klikk OK.

I Excel 2007 og 2010 klikker du på Vis-fanen og fjerner merket for Formelbjelke, rutenettlinjer og overskrifter i gruppen Vis.

8: Lås opp inputcellene

Jeg anbefaler at du beskytter arket av to grunner:

  • Du vil ikke at brukere skal endre noe annet enn verdiene i inputcellene.
  • Excel vil sykle gjennom de ulåste inndatacellene, og fungere som en inngangsveiledning som begrenser tilgang gjennom inndatacellene.

Lås opp inputcellene som følger:

  1. Velg celler C4: C7; C10: C12; C15: C18.
  2. Høyreklikk på utvalget og velg Format celler.
  3. Klikk kategorien Beskyttelse.
  4. Fjern merket for alternativet Låst.
  5. Klikk OK.

Når du har låst opp cellene, kan du slå på beskyttelsen som følger:

  1. Fra Verktøy-menyen velger du Beskyttelse og deretter Beskytt ark. I Excel 2007 og 2010 klikker du på kategorien Gjennomgang | Beskytt ark (i gruppen Endringer).
  2. I den resulterende dialogboksen skriver du inn et passord (passordet for det nedlastbare arket er passord ).
  3. Merk bare alternativet Velg ulåste celler.
  4. Klikk OK.
  5. Skriv inn passordet igjen.
  6. Klikk OK.

9: Lagre arbeidsboken

Lagre arbeidsboka på dette tidspunktet. Du trenger ikke lagre det igjen. Lukk den uten å lagre endringene etter hver bruk. Eller for å unngå å se ledeteksten som ber deg om å lagre endringer, legger du til følgende hendelsesprosedyre i ThisWorkbook-modulen:

 Privat underarbeidsbok_Forut lukk (Avbryt som boolsk) 

'Ikke lagre denne arbeidsboken. Ikke vis varsel.

Me.Saved = True

Slutt sub

Dette vil ikke avskjære en hard redning. Hvis en bruker legger inn verdier og klikker Lagre, lagrer Excel arbeidsboken.

10: Prøv det

Det er enkelt å bruke kalkulatoren. Angi passende verdier i høyre avsnitt. Figur B viser resultatet av å bruke alle tre seksjonene for å bestemme lignende resultater. Det er rom til høyre for å legge inn spesifikke instruksjoner, hvis du føler at de er nødvendige.

Figur B

Angi de aktuelle inndataverdiene for et spesifikt sparescenario.

Ved hjelp av denne enkle malen og Excel's mange økonomiske funksjoner, kan du legge til andre spesialiserte scenarier for å tilpasse kalkulatoren til å passe dine spesifikke behov.


© Copyright 2021 | pepebotifarra.com