excel-opdracht

wij snappen hier geen balvan als iemand ons kan helpen graag.

U dient als financieel en Excel-expert offertes op te stellen voor een financieel plan om de studie te financieren van het kind of de kinderen van uw cliënten. Uw cliënten gaan periodiek prenumerando een bedrag inleggen gedurende een langere periode.

U gaat daarom een Excel-werkboek maken waarmee dit efficiënt kan gebeuren zonder veel extra werk per cliënt.

Ook zorgt u ervoor dat uw assistent, die minder financiële kennis heeft en ook niet veel weet van Excel, er zoveel mogelijk toch mee overweg kan.

Voor het opstellen van een offerte dienen in samenspraak met de cliënt de volgende parameters ingevuld te worden:

· De looptijd van dit financiële plan (minimaal 10 jaar, maximaal 20 jaar en de looptijd bedraagt een geheel aantal jaren).
· Het aantal maanden dat ligt tussen 2 opeenvolgende stortingen; de mogelijkheden zijn inleg per kwartaal, half jaar of jaar.
· Het bedrag van inleg per keer (in euro); de minimuminleg is € 30 per maand (dus bij minder frequente inleg is het minimum een hoger bedrag per keer).

Vraag 1

Maak het op gebruiksvriendelijke wijze mogelijk dat uw assistent, op basis van de wensen van de cliënt, deze gegevens invoert in het Excel-werkboek. Maak dus duidelijk waar welke gegevens moeten worden ingevoerd en vermeld daarbij bovengenoemde restricties. Laat een foutmelding geven als toch nog een verkeerde parameter ingevoerd wordt. Gebruik hiervoor de if-functie.

Kies als voorbeeld reële waarden voor de looptijd en het bedrag van inleg per keer. Kies ook de frequentie van de stortingen, voor zover die hieronder niet worden voorgeschreven. Zorg wel dat alles voor willekeurige keuzes werkt.

Waar hieronder wordt gesproken over jaren worden perioden van 12 maanden bedoeld; als bijv. de startdatum van het financiële plan 1 februari 2002 is dan lopen de 'jaren' steeds van 1 februari tot 1 februari.

Vraag 2

Laat het werkboek de eindwaarde berekenen indien de bedragen worden gestort op een spaarrekening. Zorg daartoe dat het intrestpercentage (per jaar) als parameter kan worden ingevoerd. Kies als voorbeeld hiervoor een reële waarde.
Maak een tabel met voor elke storting een rij, met de tijd verstreken sinds de startdatum, de intrest die er sinds de laatste storting is bijgekomen en het tegoed vlak voor het moment van de nieuwe storting. Ga uit van samengestelde intrest.

Breid de tabel uit met kolommen voor de totale inleg en de totale intrest tot dat moment.

Aangezien alles moet werken voor willekeurige keuzes van de parameters moet de tabel voldoende rijen hebben om bij een lange looptijd en frequente stortingen toch compleet te zijn. Dat de tabel in andere gevallen te ver doorloopt is geen bezwaar (hoewel het nog mooier is om daar iets op te verzinnen, met de if-functie).

Laat de eindwaarde, met daarbij een uitsplitsing in totale inleg en totale intrest, ook apart boven de tabel verschijnen, zodat de gebruiker deze waarden niet in de tabel hoeft op te zoeken. Indien deze waarden in de tabel zijn berekend met gebruikmaking van waarden in vorige rijen kan het nodig zijn om voor de aparte vermelding een andere (rechtstreekse) formule te gebruiken. Ook is het mogelijk naar de tabelwaarden te verwijzen met de Excelfunctie 'index'.

Vraag 3

In dit onderdeel hanteren we een ander systeem van intrestvergoeding over stortingen die in de loop van een jaar plaatsvinden: over de rest van het jaar wordt voor deze stortingen uitgegaan van enkelvoudige interest; het ingevoerde intrestpercentage is daarbij het nominale percentage voor een jaar. Als een storting bijv. 3 maanden na het begin van een jaar plaatsvindt resteren er dat jaar nog 9 maanden, voor die storting is de intrest over die 9 maanden 9/12 deel van het jaarpercentage. Het tegoed dat aan het eind van het jaar is opgebouwd levert in het volgende jaar in zijn geheel intrest op, in zoverre is er toch weer sprake van samengestelde intrest.

Maak nu een tabel met slechts voor elk jaar een rij, met de tijd verstreken sinds de startdatum, de intrest die er het afgelopen jaar in totaal is bijgekomen, het tegoed vlak voor het moment van de nieuwe storting, en weer kolommen voor de totale inleg en de totale intrest tot dat moment.

Vraag 4

Kies een looptijd, een nominaal intrestpercentage en een inlegbedrag, en bereken voor elk van de drie inlegfrequenties (per kwartaal, half jaar en jaar) het effectief intrestpercentage per jaar. Doe dit met gebruikmaking van de resultaten van de vragen 2 en 3, in combinatie met de Solver.

Vraag 5

Ga uit van de situatie geschetst in Vraag 2.

De cliënt geeft nu niet aan wat hij steeds wil inleggen, maar over welk bedrag hij aan het eind van de looptijd wenst te beschikken. Zorg dat dit bedrag als parameter kan worden ingevoerd en dat dan automatisch op het scherm verschijnt wat de inleg moet zijn voor deze cliënt, naar boven afgerond op een veelvoud van € 5. Aanwijzingen: Als de berekeningen van vraag 2 worden toegepast op een inleg van bijv. € 1000, dan volgt daaruit de eindwaarde. Een hogere of lagere inleg geeft een evenredig hogere of lagere eindwaarde, dus zo kan je Excel laten bepalen welke inleg de gewenste eindwaarde oplevert. Naar boven afronden op veelvouden van € 5 kan bijv. met de functie ‘ceiling’.

Vraag 6

Een andere optie is een zgn. Studiegroeiplan af te sluiten. De inleg is weer als in vraag 1, maar wordt in dit geval in een beleggingsfonds gestort. Aan het eind van elk jaar wordt 0,6% van de totale waarde van de beleggingen aan kosten ingehouden. Doe een aanname voor het bruto-rendement per jaar (d.i. het rendement vòòr aftrek van de 0,6% kosten), kies een looptijd en een bedrag van inleg en bereken daarbij het effectief nettorendement per jaar, d.w.z. het intrestpercentage bij een spaarrekening dat hetzelfde eindkapitaal zou opleveren.

Doe dit voor het geval van een jaarlijkse storting, en voor het geval van een storting elk kwartaal. Aanwijzing: om te bepalen of het aantal verstreken kwartalen een viervoud is (en er dus kosten worden ingehouden) kan de Excel-functie ‘mod’ worden gebruikt.

Bereken tevens in beide gevallen het bruto rendement per jaar waarbij het netto rendement nul is, dus waarbij de waarde aan het eind van de looptijd gelijk is aan het totaal ingelegde bedrag.

Maak een grafiek die voor een van de berekende gevallen het verloop van de beleggingswaarde als functie van de tijd laat zien.